ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / история
8 сообщений из 8, страница 1 из 1
история
    #39749152
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
Нужна идея, как правильно написать

есть линкед сервер
через него читаю
таблица данных и таблица истории (история - что-то вроде EAV)
Надо по каждой строке данных подтянуть историю - значение параметра на начало периода и на конец периода и дату изменения этих параметров на начало и конец периода

Собственно, данные я получил
Но напрягает читать эти данные тучей скалярных подзапросов, да еще по линкедсерверу
при том, что основной запрос будет из 20 тысяч строк - т.е. 20000 * 16 (4 параметра * 4 значения) - и получаем 320 тысяч скалярных подзапросов через линкед сервер

что весьма напряжно

Сама таблица истории пока пустая
но в ней будут десятки миллионов строк уже через полгода
Индексы в ней есть, и пообещали добавить любые, какие будут нужны
 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
declare
    @p_date_from datetime = {ts '2018-12-17 11:00:00'},
    @p_date_to   datetime = {ts '2018-12-17 17:00:00'};

with
data as
(
select 'CI1189072' id  union all
select 'CI1190919' id  union all
select 'CI1191312' id  union all
select 'CI1191970' id  union all
select 'CI1192025' id  union all
select 'CI1192060' id  union all
select 'CI1193174' id  union all
select 'CI1197787' id  union all
select 'CI1197822' id  union all
select 'CI1198778' id),
hist as
(
select 'CI1191970' id, { ts '2018-12-17 08:41:05'} created_time, N'Счетчик А4 Ч/Б:' field_name, '21770' new_simple_val union all
select 'CI1191970' id, { ts '2018-12-17 08:41:05'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1191312' id, { ts '2018-12-17 09:00:13'} created_time, N'Счетчик А4 Ч/Б:' field_name, '66601' new_simple_val union all
select 'CI1191312' id, { ts '2018-12-17 09:00:13'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1189072' id, { ts '2018-12-17 12:30:03'} created_time, N'Счетчик А4 Ч/Б:' field_name, '1268820' new_simple_val union all
select 'CI1190919' id, { ts '2018-12-17 12:48:18'} created_time, N'Счетчик А4 Ч/Б:' field_name, '17375' new_simple_val union all
select 'CI1190919' id, { ts '2018-12-17 12:48:18'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1192025' id, { ts '2018-12-17 12:50:25'} created_time, N'Счетчик А4 Ч/Б:' field_name, '14303' new_simple_val union all
select 'CI1192025' id, { ts '2018-12-17 12:50:25'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1192060' id, { ts '2018-12-17 12:56:18'} created_time, N'Счетчик А4 Ч/Б:' field_name, '42852' new_simple_val union all
select 'CI1192060' id, { ts '2018-12-17 12:56:18'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1197822' id, { ts '2018-12-17 01:27:46'} created_time, N'Счетчик А4 Ч/Б:' field_name, '3068' new_simple_val union all
select 'CI1197822' id, { ts '2018-12-17 01:27:46'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1198778' id, { ts '2018-12-17 03:41:38'} created_time, N'Счетчик А4 Ч/Б:' field_name, '9254' new_simple_val union all
select 'CI1197787' id, { ts '2018-12-17 03:35:02'} created_time, N'Счетчик А4 Ч/Б:' field_name, '8870' new_simple_val union all
select 'CI1197787' id, { ts '2018-12-17 03:35:02'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1193174' id, { ts '2018-12-17 04:13:26'} created_time, N'Счетчик А4 ЦВ:' field_name, '1' new_simple_val
)
select data.id,
       (
        select top 1
               new_simple_val
          from hist
         where hist.FIELD_NAME = N'Счетчик А4 Ч/Б:'
           and hist.id = data.id
           and hist.created_time <= @p_date_from
          order by hist.created_time desc
       )     [Счетчик А4 Ч/Б: на начало периода отбора],
       (
        select top 1
               new_simple_val
          from hist
         where hist.FIELD_NAME = N'Счетчик А4 Ч/Б:'
           and hist.id = data.id
           and hist.created_time <= @p_date_to
          order by hist.created_time desc
       )     [Счетчик А4 Ч/Б: на конец периода отбора]

  from data

idСчетчик А4 Ч/Б: на начало периода отбораСчетчик А4 Ч/Б: на конец периода отбораCI1189072NULL1268820CI1190919NULL17375CI11913126660166601CI11919702177021770CI1192025NULL14303CI1192060NULL42852CI1193174NULLNULLCI119778788708870CI119782230683068CI119877892549254
...
Рейтинг: 0 / 0
17.12.2018, 19:54
| Ответить | Цитировать | Написать  
история
    #39749154
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
сейчас проверил - на чтение одного скаляра на 11 тысяч строк ушло 43 сек
т.е. 43*16 сек - мне явно не подойдет
...
Рейтинг: 0 / 0
17.12.2018, 20:01
| Ответить | Цитировать | Написать  
история
    #39749169
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
andreymxНо напрягает читать эти данные тучей скалярных подзапросов, да еще по линкедсерверу
при том, что основной запрос будет из 20 тысяч строк - т.е. 20000 * 16 (4 параметра * 4 значения)Ну так это особенность EAV. Либо так, либо поддерживайте на стороне линкеда транспонированный EAV с нужной гранулярностью по дате.
Из 4*4 можно легко получить 4*2, перенеся коррелированные подзапросы из select в outer apply и объединив в одном получение значения и даты.
Индекс для этого хозяйства нужен (FIELD_NAME, id, created_time) include (new_simple_val). FIELD_NAME и id можно в другом порядке.
...
Рейтинг: 0 / 0
17.12.2018, 20:59
| Ответить | Цитировать | Написать  
история
    #39749177
Фотография alexeyvg
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
alexeyvg
Участник
andreymxНадо по каждой строке данных подтянуть историю - значение параметра на начало периода и на конец периода и дату изменения этих параметров на начало и конец периода

Собственно, данные я получил
Но напрягает читать эти данные тучей скалярных подзапросов, да еще по линкедсерверуНе пойму, а что мешает получить эти данные одним запросом, а не кучей?
...
Рейтинг: 0 / 0
17.12.2018, 21:15
| Ответить | Цитировать | Написать  
история
    #39749179
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Или можете попробовать примерно так:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
declare
    @p_date_from datetime = {ts '2018-12-17 11:00:00'},
    @p_date_to   datetime = {ts '2018-12-17 17:00:00'};

with
data as
(
select 'CI1189072' id  union all
select 'CI1190919' id  union all
select 'CI1191312' id  union all
select 'CI1191970' id  union all
select 'CI1192025' id  union all
select 'CI1192060' id  union all
select 'CI1193174' id  union all
select 'CI1197787' id  union all
select 'CI1197822' id  union all
select 'CI1198778' id),
hist as
(
select 'CI1191970' id, { ts '2018-12-17 08:41:05'} created_time, N'Счетчик А4 Ч/Б:' field_name, '21770' new_simple_val union all
select 'CI1191970' id, { ts '2018-12-17 08:41:05'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1191312' id, { ts '2018-12-17 09:00:13'} created_time, N'Счетчик А4 Ч/Б:' field_name, '66601' new_simple_val union all
select 'CI1191312' id, { ts '2018-12-17 09:00:13'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1189072' id, { ts '2018-12-17 12:30:03'} created_time, N'Счетчик А4 Ч/Б:' field_name, '1268820' new_simple_val union all
select 'CI1190919' id, { ts '2018-12-17 12:48:18'} created_time, N'Счетчик А4 Ч/Б:' field_name, '17375' new_simple_val union all
select 'CI1190919' id, { ts '2018-12-17 12:48:18'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1192025' id, { ts '2018-12-17 12:50:25'} created_time, N'Счетчик А4 Ч/Б:' field_name, '14303' new_simple_val union all
select 'CI1192025' id, { ts '2018-12-17 12:50:25'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1192060' id, { ts '2018-12-17 12:56:18'} created_time, N'Счетчик А4 Ч/Б:' field_name, '42852' new_simple_val union all
select 'CI1192060' id, { ts '2018-12-17 12:56:18'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1197822' id, { ts '2018-12-17 01:27:46'} created_time, N'Счетчик А4 Ч/Б:' field_name, '3068' new_simple_val union all
select 'CI1197822' id, { ts '2018-12-17 01:27:46'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1198778' id, { ts '2018-12-17 03:41:38'} created_time, N'Счетчик А4 Ч/Б:' field_name, '9254' new_simple_val union all
select 'CI1197787' id, { ts '2018-12-17 03:35:02'} created_time, N'Счетчик А4 Ч/Б:' field_name, '8870' new_simple_val union all
select 'CI1197787' id, { ts '2018-12-17 03:35:02'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1193174' id, { ts '2018-12-17 04:13:26'} created_time, N'Счетчик А4 ЦВ:' field_name, '1' new_simple_val
)
select
 d.id, t.*
from
 data d outer apply
 (
  select
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.created_time end),
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.new_simple_val end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.created_time end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.new_simple_val end),
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.created_time end),
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.new_simple_val end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.created_time end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.new_simple_val end)
  from
   (
    select
     h.field_name, h.created_time, h.new_simple_val,
     row_number() over (partition by h.field_name order by h.created_time) as rn1,
     row_number() over (partition by h.field_name order by h.created_time desc) as rn2
    from
     (values (N'Счетчик А4 ЦВ:'), (N'Счетчик А4 Ч/Б:')) a(field_name) join
     hist h on h.field_name = a.field_name and h.id = d.id
    where
     h.created_time between @p_date_from and @p_date_to 
   ) x
  where
   x.rn1 = 1 or x.rn2 = 1
 ) t(f1, f2, f3, f4, f5, f6, f7, f8);

...
Рейтинг: 0 / 0
17.12.2018, 21:22
| Ответить | Цитировать | Написать  
история
    #39749291
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
Можно, как предлагает alexeyvg , и одним запросом:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
declare
    @p_date_from datetime = {ts '2018-12-17 11:00:00'},
    @p_date_to   datetime = {ts '2018-12-17 17:00:00'};

with
data as
(
select 'CI1189072' id  union all
select 'CI1190919' id  union all
select 'CI1191312' id  union all
select 'CI1191970' id  union all
select 'CI1192025' id  union all
select 'CI1192060' id  union all
select 'CI1193174' id  union all
select 'CI1197787' id  union all
select 'CI1197822' id  union all
select 'CI1198778' id),
hist as
(
select 'CI1191970' id, { ts '2018-12-17 08:41:05'} created_time, N'Счетчик А4 Ч/Б:' field_name, '21770' new_simple_val union all
select 'CI1191970' id, { ts '2018-12-17 08:41:05'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1191312' id, { ts '2018-12-17 09:00:13'} created_time, N'Счетчик А4 Ч/Б:' field_name, '66601' new_simple_val union all
select 'CI1191312' id, { ts '2018-12-17 09:00:13'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1189072' id, { ts '2018-12-17 12:30:03'} created_time, N'Счетчик А4 Ч/Б:' field_name, '1268820' new_simple_val union all
select 'CI1190919' id, { ts '2018-12-17 12:48:18'} created_time, N'Счетчик А4 Ч/Б:' field_name, '17375' new_simple_val union all
select 'CI1190919' id, { ts '2018-12-17 12:48:18'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1192025' id, { ts '2018-12-17 12:50:25'} created_time, N'Счетчик А4 Ч/Б:' field_name, '14303' new_simple_val union all
select 'CI1192025' id, { ts '2018-12-17 12:50:25'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1192060' id, { ts '2018-12-17 12:56:18'} created_time, N'Счетчик А4 Ч/Б:' field_name, '42852' new_simple_val union all
select 'CI1192060' id, { ts '2018-12-17 12:56:18'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1197822' id, { ts '2018-12-17 01:27:46'} created_time, N'Счетчик А4 Ч/Б:' field_name, '3068' new_simple_val union all
select 'CI1197822' id, { ts '2018-12-17 01:27:46'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1198778' id, { ts '2018-12-17 03:41:38'} created_time, N'Счетчик А4 Ч/Б:' field_name, '9254' new_simple_val union all
select 'CI1197787' id, { ts '2018-12-17 03:35:02'} created_time, N'Счетчик А4 Ч/Б:' field_name, '8870' new_simple_val union all
select 'CI1197787' id, { ts '2018-12-17 03:35:02'} created_time, N'Счетчик А4 ЦВ:' field_name, '0' new_simple_val union all
select 'CI1193174' id, { ts '2018-12-17 04:13:26'} created_time, N'Счетчик А4 ЦВ:' field_name, '1' new_simple_val
)
select
 d.id, t.f1, t.f2, t.f3, t.f4, t.f5, t.f6, t.f7, t.f8
from
 data d left join
 (
  select
   x.id,
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.created_time end),
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.new_simple_val end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.created_time end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 ЦВ:' then x.new_simple_val end),
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.created_time end),
   max(case when x.rn1 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.new_simple_val end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.created_time end),
   max(case when x.rn2 = 1 and x.field_name = N'Счетчик А4 Ч/Б:' then x.new_simple_val end)
  from
   (
    select
     h.id, h.field_name, h.created_time, h.new_simple_val,
     row_number() over (partition by h.id, h.field_name order by h.created_time) as rn1,
     row_number() over (partition by h.id, h.field_name order by h.created_time desc) as rn2
    from
     (values (N'Счетчик А4 ЦВ:'), (N'Счетчик А4 Ч/Б:')) a(field_name) join
     hist h on h.field_name = a.field_name
    where
     h.created_time between @p_date_from and @p_date_to 
   ) x
  where
   x.rn1 = 1 or x.rn2 = 1
  group by
   x.id
 ) t(id, f1, f2, f3, f4, f5, f6, f7, f8) on t.id = d.id;

...
Рейтинг: 0 / 0
18.12.2018, 10:33
| Ответить | Цитировать | Написать  
история
    #39749316
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
коллеги, всем спасибо
как написать общий запрос я представляю
Думаю, что будет с ресурсами и временем выполнения

Или на пустой БД об этом думать рано?
...
Рейтинг: 0 / 0
18.12.2018, 11:09
| Ответить | Цитировать | Написать  
история
    #39749320
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
andreymxИли на пустой БД об этом думать рано?Именно.
...
Рейтинг: 0 / 0
18.12.2018, 11:14
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / история
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]