Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Направьте на путь истинный в составлении запроса =)  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 106
Добрый день. Помогите пожалуйста правильно составить запрос.
Есть несколько таблиц.
Таблица А. Надо получить сумму ячейки Status по определенному [OID] за определенный промежуток даты:
ID            | OID           | Status | Date
C9E846AA-AF65 | 2B3290FD-B032 | 13     | 2019-02-26 14:10:07
8FD3A842-9CD2 | 2B3290FD-B032 | 2      | 2019-02-26 14:9:41
DF500B3A-7EEE | E3E5F3C0-A20A | 7      | 2019-02-26 14:8:23
5BFC4A51-D1BF | E3E5F3C0-A20A | 6      | 2019-02-26 14:5:53
71CE837D-959D | AA9ADECE-F455 | 27     | 2019-02-26 14:3:17


Таблица Б. Тут нужно получить последнюю дату где [view] = простой, которую надо игнорировать во время опроса Таблицы А:
ID            | OID           | View    | StartDate
891DB1F0-4024 | E3E5F3C0-A20A | простой | 2019-02-26 14:06:39
2A174A77-AA31 | E3E5F3C0-A20A | не трогать| 2019-02-26 15:06:39


Таблица В. Тут список [OID] которые нужно получить во время всего опроса:
ID            | IP      | OID
84E034D7-2D76 | 1.2.3.4 | 2B3290FD-B032
EA689B47-2F33 | 1.2.3.4 | E3E5F3C0-A20A
4481D885-A079 | 1.2.3.4 | AA9ADECE-F455


То-есть:
1) берем наш [IP] = 1.2.3.4 .
2) Вытягиваем 3 [OID] из Таблицы В.
3) Суммируем колонку Status из Таблицы А, каждый [OID] по отдельности за определенный период времени.
4) Во время суммирования Status из Таблица А игнорируем время превышающее дату из Таблицы Б, но только у [OID] который прописан в Таблице Б.

Вот с четвертым пунктом у меня проблемы. Может можно как то сделать это одним запросом?

Сейчас запрос без четвертого пункта выглядит так:
SELECT sum(TA.Status) AS status, TB.OID AS oid
FROM ТаблицаА AS TA
LEFT JION ТаблицаВ AS TB ON TA.OID = TB.OID
WHERE TB.IP  =  '1.2.3.4'
AND TA.Date BETWEEN DATEADD(hour, 7, DATEDIFF(dd, 0, GETDATE()))
AND DATEADD(hour, 19, DATEDIFF(dd, 0, GETDATE()))
GROUP BY TB.OID


Может можно как то добавить к запросу:
AND TA.Date NOT BETWEEN MAX(ТаблицаБ.Start_date) WHERE ТаблицаБ.View = 'простой' AND ТаблицаБ.OID = TB.OID
26 фев 19, 08:37    [21819316]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1466
Hopfen_Artur,

вам, для начала, нужно посчитать отдельно все агрегаты по требуемым промежуткам, а уж затем - собирать результат угрегации данных в единую выборку. Иначе получите многократное задублирование данных и неверные суммы..
26 фев 19, 08:44    [21819319]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 106
Щукина Анна, Я мягко говоря не силен в запросах, приведите пример пожалуйста. Я уж как нибудь подстрою под свои нужды =)
26 фев 19, 08:48    [21819323]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
Как-то так
AND TA.Date <= (
 select MAX(Start_date) from ТаблицаБ WHERE View = 'простой' AND OID = TB.OID and
  Start_date between DATEADD(hour, 7, DATEDIFF(dd, 0, GETDATE()))
  AND DATEADD(hour, 19, DATEDIFF(dd, 0, GETDATE()))
)
26 фев 19, 09:19    [21819346]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
student-uni
Member

Откуда: Одесса
Сообщений: 1102
Тебе Таблицу Б надо отдельно спартиционировать (чтоб остались только максимальные даты для каждого ОИД -смотри код внизу)

А потом NOT EXIST-ом сказать : "Нехочу тех из парициона, которые удовлетворяют (или наоборот неудовлетворяют) таким то требованиям"

SELECT sum(TA.Status) AS status, TB.OID AS oid
FROM ТаблицаА AS TA
LEFT JION ТаблицаВ AS TB ON TA.OID = TB.OID

WHERE TB.IP  =  '1.2.3.4'
AND TA.Date BETWEEN DATEADD(hour, 7, DATEDIFF(dd, 0, GETDATE()))
AND DATEADD(hour, 19, DATEDIFF(dd, 0, GETDATE()))


AND NOT EXISTS 
( 

  SELECT * FROM 
   (
    /* только последняя дата для каждого OID */
    SELECT TOP (1) WITH TIES OID, Date
    FROM TABLITSA_B
    [view] = 'простой'
    ORDER BY
    Date OVER(PARTITION BY OID );

    ) AS LastOidDate
    WHERE LastOidDate.OID = TA.OID AND TA.Date > LastOidDate.Date

)
GROUP BY TB.OID
26 фев 19, 09:34    [21819359]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
student-uni
Member

Откуда: Одесса
Сообщений: 1102
ORDER BY Date desc
и точка с запятой лишние
26 фев 19, 09:37    [21819363]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 106
student-uni,

Спасибо, сейчас буду пробовать =)
26 фев 19, 09:40    [21819364]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 106
student-uni,

Вот так если написать выходит ошибка:

неправильный синтаксис около ключевого слова "OVER"

SELECT sum(TA.Status) AS status, TB.OID AS oid
FROM ТаблицаА AS TA
LEFT JION ТаблицаВ AS TB ON TA.OID = TB.OID

WHERE TB.IP  =  '1.2.3.4'
AND TA.Date BETWEEN DATEADD(hour, 7, DATEDIFF(dd, 0, GETDATE()))
AND DATEADD(hour, 19, DATEDIFF(dd, 0, GETDATE()))


AND NOT EXISTS 
( 

  SELECT * FROM 
   (
    SELECT TOP (1) WITH TIES OID, Date
    FROM TABLITSA_B
    WHERE [view] = 'простой'
    ORDER BY Date DESC 
    OVER(PARTITION BY OID )
    ) AS LastOidDate
    WHERE LastOidDate.OID = TA.OID AND TA.Date > LastOidDate.Date

)
GROUP BY TB.OID
26 фев 19, 09:56    [21819382]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Hopfen_Artur,

а зачем вы туда пишете? OVER(PARTITION BY OID )?
26 фев 19, 09:59    [21819383]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 106
TaPaK,

Ну вот я в select вставил, ошибок нет, но и выборки нет. Плохо разбираюсь я, учусь только:
SELECT sum(TA.Status) AS status, TB.OID AS oid
FROM ТаблицаА AS TA
LEFT JION ТаблицаВ AS TB ON TA.OID = TB.OID

WHERE TB.IP  =  '1.2.3.4'
AND TA.Date BETWEEN DATEADD(hour, 7, DATEDIFF(dd, 0, GETDATE()))
AND DATEADD(hour, 19, DATEDIFF(dd, 0, GETDATE()))


AND NOT EXISTS 
( 

  SELECT * FROM 
   (
    SELECT OID, MAX(Date) OVER(PARTITION BY OIDORDER BY Date DESC) AS Date 
    FROM TABLITSA_B
    WHERE [view] = 'простой'
    ) AS LastOidDate
    WHERE LastOidDate.OID = TA.OID AND TA.Date > LastOidDate.Date

)
GROUP BY TB.OID
26 фев 19, 10:06    [21819392]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
student-uni
Member

Откуда: Одесса
Сообщений: 1102
Hopfen_Artur,

SELECT sum(TA.Status) AS status, TB.OID AS oid
FROM ТаблицаА AS TA
LEFT JION ТаблицаВ AS TB ON TA.OID = TB.OID

WHERE TB.IP = '1.2.3.4'
AND TA.Date BETWEEN DATEADD(hour, 7, DATEDIFF(dd, 0, GETDATE()))
AND DATEADD(hour, 19, DATEDIFF(dd, 0, GETDATE()))


AND NOT EXISTS
(

SELECT * FROM
(
SELECT TOP (1) WITH TIES OID, Date
FROM TABLITSA_B
WHERE [view] = 'простой'

order by row_number() over (partition by OID order by Date desc)

) AS LastOidDate
WHERE LastOidDate.OID = TA.OID AND TA.Date > LastOidDate.Date

)
GROUP BY TB.OID
26 фев 19, 10:07    [21819394]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 106
student-uni,

выводит пустой результат. В таблицеБ может вообще не быть данных, может из за этого?
26 фев 19, 10:49    [21819442]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
student-uni
Member

Откуда: Одесса
Сообщений: 1102
Hopfen_Artur,
что выдает
SELECT TOP (1) WITH TIES OID, Date
FROM TABLITSA_B
WHERE [view] = 'простой'

order by row_number() over (partition by OID order by Date desc)
26 фев 19, 10:50    [21819443]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
Hopfen_Artur,

declare @ds datetime = dateadd(hour, 7, cast(cast(getdate() as date) as datetime)), @de datetime = dateadd(hour, 19, cast(cast(getdate() as date) as datetime));

select
 sum(TA.Status) as status,
 TB.OID as oid
from
 ТаблицаА AS TA left jion
 ТаблицаВ AS TB ON TA.OID = TB.OID left join
 (
  select
   OID,
   max(StartDate)
  from
   ТаблицаБ 
  where 
   StartDate between @ds and @de
  group by
   OID 
 ) a(OID, StartDate)
where
 TB.IP  =  '1.2.3.4'
 and TA.Date between @ds and @de
 and (a.StartDate is null or TA.Date < a.StartDate)
group by
 TB.OID;
26 фев 19, 11:07    [21819462]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
Поправочка
declare @ds datetime = dateadd(hour, 7, cast(cast(getdate() as date) as datetime)), @de datetime = dateadd(hour, 19, cast(cast(getdate() as date) as datetime));

select
 sum(TA.Status) as status,
 TB.OID as oid
from
 ТаблицаА AS TA left jion
 ТаблицаВ AS TB ON TA.OID = TB.OID left join
 (
  select
   OID,
   max(StartDate)
  from
   ТаблицаБ 
  where 
   StartDate between @ds and @de
  group by
   OID 
 ) a(OID, StartDate) on a.OID = TA.OID
where
 TB.IP  =  '1.2.3.4'
 and TA.Date between @ds and @de
 and (a.StartDate is null or TA.Date < a.StartDate)
group by
 TB.OID;
26 фев 19, 11:14    [21819467]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 106
student-uni
Hopfen_Artur,
что выдает
SELECT TOP (1) WITH TIES OID, Date
FROM TABLITSA_B
WHERE [view] = 'простой'

order by row_number() over (partition by OID order by Date desc)


выдает такой результат:
OID             | Date
E0472205-8C90	|2019-02-25 13:26:34.000
b8bbc8b6-f36f	|2018-11-28 14:35:07.000
0ada876a-4c54	|2018-12-05 18:12:12.000
0c24d28a-cc4a	|2018-11-27 14:29:24.000
5b22fe0e-36a5	|2018-12-04 16:00:45.000
93e8fecb-8782	|2019-02-05 08:51:22.000
26 фев 19, 11:21    [21819477]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 106
invm,

спасибо, сейчас попробую
26 фев 19, 11:21    [21819478]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
student-uni
Member

Откуда: Одесса
Сообщений: 1102
Hopfen_Artur,

Left Join не нужен

и вместо TB.OID
ставим TA.OID


SELECT sum(TA.Status) AS status, TA.OID AS oid
FROM ТаблицаА AS TA
WHERE TB.IP  =  '1.2.3.4'
AND TA.Date BETWEEN DATEADD(hour, 7, DATEDIFF(dd, 0, GETDATE()))
AND DATEADD(hour, 19, DATEDIFF(dd, 0, GETDATE()))

AND NOT EXISTS
(

SELECT * FROM
(
SELECT TOP (1) WITH TIES OID, Date
FROM TABLITSA_B
WHERE [view] = 'простой'

order by row_number() over (partition by OID order by Date desc)

) AS LastOidDate
WHERE LastOidDate.OID = TA.OID AND TA.Date > LastOidDate.Date

GROUP BY TA.OID
26 фев 19, 11:34    [21819496]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 106
student-uni,

Спасибо вам большое. Я попробую доработать вариант invm . У него учитывается промежуток времени Таблицы Б.

invm,

Запрос работает ,спасибо. Но подскажи пожалуйста как добавить условие.

Мы сейчас берем максимальную дату с соответствующим OID. Но нужно игнорировать ее, если View != 'простой'. То-есть получили максимальную дату и посмотрели колонку View. Если не соответствует, игнорируем. Можно ли так сделать? Может сам додумаюсь, заранее спасибо =)
26 фев 19, 11:39    [21819502]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
Hopfen_Artur
Но нужно игнорировать ее, если View != 'простой'
Добавить соответствующее условие в where запроса к ТаблицаБ.
26 фев 19, 11:48    [21819518]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 106
invm
Hopfen_Artur
Но нужно игнорировать ее, если View != 'простой'
Добавить соответствующее условие в where запроса к ТаблицаБ.


А он разве не будет искать последнюю дату именно где View != 'простой' ?
Допустим ТаблицаБ:
ID            | OID           | View    | StartDate
891DB1F0-4024 | E3E5F3C0-A20A | не трогать | 2019-02-26 16:06:39
2A174A77-AA31 | E3E5F3C0-A20A | простой| 2019-02-26 15:06:39


Если я просто напишу WHERE View = 'простой' он выберет вторую строку, хотя в таблице есть дата с более максимальным временем.

А если напишу WHERE View != 'простой' то при такой таблице :
ID            | OID           | View    | StartDate
891DB1F0-4024 | E3E5F3C0-A20A | простой | 2019-02-26 16:06:39
2A174A77-AA31 | E3E5F3C0-A20A | не трогать| 2019-02-26 15:06:39


Выберет вторую строку, что в корне не верно. Ведь нам нужны именно View == 'простой'.
26 фев 19, 12:04    [21819546]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3440
Hopfen_Artur
student-uni,

Спасибо вам большое. Я попробую доработать вариант invm . У него учитывается промежуток времени Таблицы Б.

invm,

Запрос работает ,спасибо. Но подскажи пожалуйста как добавить условие.

Мы сейчас берем максимальную дату с соответствующим OID. Но нужно игнорировать ее, если View != 'простой'. То-есть получили максимальную дату и посмотрели колонку View. Если не соответствует, игнорируем. Можно ли так сделать? Может сам додумаюсь, заранее спасибо =)


Держись, юзер! не выдавай тайну, какая версия сервера!
26 фев 19, 12:10    [21819555]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
invm
Member

Откуда: Москва
Сообщений: 9115
Hopfen_Artur
А он разве не будет искать последнюю дату именно где View != 'простой' ?
Будет.

Вам нужно игнорировать, когда у максимальной StartDate для OID View = 'простой'?
Тогда так:
declare @ds datetime = dateadd(hour, 7, cast(cast(getdate() as date) as datetime)), @de datetime = dateadd(hour, 19, cast(cast(getdate() as date) as datetime));

with tb as
(
  select
   OID, StartDate, View,
   row_number() over (partition by OID order by StartDate desc) as rn
  from
   ТаблицаБ 
  where 
   StartDate between @ds and @de
)
select
 sum(TA.Status) as status,
 TB.OID as oid
from
 ТаблицаА AS TA left jion
 ТаблицаВ AS TB ON TA.OID = TB.OID left join
 tb on tb.rn = 1 and tb.OID = TA.OID and tb.View = 'простой'
where
 TB.IP  =  '1.2.3.4'
 and TA.Date between @ds and @de
 and (a.StartDate is null or TA.Date < a.StartDate)
group by
 TB.OID;
26 фев 19, 12:32    [21819592]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 106
invm,

Спасибо большое, очень выручили . Буду разбираться как это работает.
26 фев 19, 13:16    [21819638]     Ответить | Цитировать Сообщить модератору
 Re: Направьте на путь истинный в составлении запроса =)  [new]
Hopfen_Artur
Member

Откуда:
Сообщений: 106
Еще такой вопрос. Допустим в промежутке выборки даты вообще нет данных:
dateadd(hour, 7, cast(cast(getdate() as date) as datetime)), @de datetime = dateadd(hour, 19, cast(cast(getdate() as date) as datetime));

допустим наша Таблица В
ID            | IP      | OID
84E034D7-2D76 | 1.2.3.4 | 2B3290FD-B032
EA689B47-2F33 | 1.2.3.4 | E3E5F3C0-A20A
4481D885-A079 | 1.2.3.4 | AA9ADECE-F455


По OID AA9ADECE-F455 за промежуток времени вообще нет данных. Результат выборки будет такой:
status | oid
283    | 2B3290FD-B032
130    | E3E5F3C0-A20A


А хотелось бы такой результат:
status | oid
283    | 2B3290FD-B032
130    | E3E5F3C0-A20A
0      | AA9ADECE-F455


Я пробовал так:
select sum(COALESCE(TA.Status, 0)) as status, TB.OID as oid


Но оно видимо предполагает что данные за промежуток есть, но они равны null.

Может кто сталкивался с подобной проблемой?
26 фев 19, 14:20    [21819740]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить