#sql #sql-server #sql-server-2012
#sql #sql-сервер #sql-server-2012
Вопрос:
Таблица: у меня есть таблица базы данных mytable1 в SQL Server 2012. Определение таблицы
Column_name Type Length Nullable
ts datetime 8 no
s_no int 4 no
calls int 4 yes
DDL: И создал его с помощью
CREATE TABLE mytable1(
ts DATETIME NOT NULL,
s_no INT NOT NULL,
calls INT
);
Заполнение данных
INSERT INTO mytable1 (ts, s_no, calls)
VALUES
('2016-10-14 10:04:01.000', 3, 56),
('2016-10-14 10:04:01.000', 4, 145),
('2016-10-14 10:09:00.000', 3, 143),
('2016-10-14 10:09:00.000', 4, 329),
('2016-10-14 10:14:01.000', 3, 0),
('2016-10-14 10:14:01.000', 4, 49),
('2016-10-14 10:19:00.000', 3, 6),
('2016-10-14 10:19:00.000', 4, 16),
('2016-10-14 10:24:01.000', 3, 22),
('2016-10-14 10:24:01.000', 4, 28),
('2016-10-14 10:29:00.000', 3, 4),
('2016-10-14 10:29:00.000', 4, 7),
('2016-10-14 10:34:00.000', 3, 14),
('2016-10-14 10:34:00.000', 4, 9),
('2016-10-14 10:38:59.000', 3, 39),
('2016-10-14 10:38:59.000', 4, 391),
('2016-10-14 10:44:01.000', 3, 3),
('2016-10-14 10:44:01.000', 4, 31),
('2016-10-14 10:49:01.000', 3, 116),
('2016-10-14 10:49:01.000', 4, 52),
('2016-10-14 10:54:00.000', 3, 75),
('2016-10-14 10:54:00.000', 4, 8),
('2016-10-14 10:59:00.000', 3, 16),
('2016-10-14 10:59:00.000', 4, 8),
('2016-10-14 11:04:01.000', 3, 23),
('2016-10-14 11:04:01.000', 4, 13);
Я пытаюсь разделить временную метку на 30-минутное окно, а затем выбрать самую высокую временную метку из каждого 30-минутного окна. Также я хочу выбрать все разные значения s_no для максимальной отметки времени в 30 минут.
Существующий запрос: у меня есть запрос, который выполняет это
select m.s_no, m.bucket_window, max(m.ts) ts
from (
select m.*, datepart(hour, m.ts)*2 floor(datepart(minute, m.ts)/30) bucket_window
from mytable1 m
where m.ts >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
) m
group by m.s_no, m.bucket_window;
Результат: Это дает результат в виде
s_no bucket_window ts
3 20 2016-10-14 10:29:00.000
4 20 2016-10-14 10:29:00.000
3 21 2016-10-14 10:59:00.000
4 21 2016-10-14 10:59:00.000
3 22 2016-10-14 11:04:01.000
4 22 2016-10-14 11:04:01.000
Теперь я хочу улучшить этот запрос и добавить вызовы столбцов в приведенный выше результат. Этот столбец должен иметь значение из столбца calls таблицы mytable1, где комбинация s_no и ts из приведенного выше результата совпадает с комбинацией s_no и ts из таблицы mytable1.
Ожидаемый результат: итак, результат, который я хочу сейчас, это
s_no bucket_window ts calls
3 20 2016-10-14 10:29:00.000 4
4 20 2016-10-14 10:29:00.000 7
3 21 2016-10-14 10:59:00.000 16
4 21 2016-10-14 10:59:00.000 8
3 22 2016-10-14 11:04:01.000 23
4 22 2016-10-14 11:04:01.000 13
Я попытался использовать join, но не могу объединить запрос с правильным синтаксисом:
Что я пробовал:
select m.s_no, m.bucket_window, max(m.ts) ts, i.calls
from (
select m.*, datepart(hour, m.ts)*2 floor(datepart(minute, m.ts)/30) bucket_window
from mytable1 m
where m.ts >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
) m
LEFT JOIN mytable1 i
ON max(m.ts) = i.ts--OR (m.ts) = i.ts
group by m.s_no, m.bucket_window, i.calls
Пожалуйста, подскажите мне, как я могу изменить этот существующий запрос, чтобы получить ожидаемый результат.
Поскольку этот существующий запрос используется в производстве в течение длительного времени и используется в качестве вложенного запроса в других динамически генерируемых запросах, я не хочу полностью изменять его, чтобы получить ожидаемый результат.
Ответ №1:
Попробуйте это
SELECT M1.*,
(SELECT calls From mytable1 M2 where M2.ts=M1.ts and M2.s_no=M1.s_no) as calls
FROM
(
select m.s_no, m.bucket_window, max(m.ts) ts
from (
select m.*, datepart(hour, m.ts)*2 floor(datepart(minute, m.ts)/30) bucket_window
from mytable1 m
where m.ts >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
) m
group by m.s_no, m.bucket_window
) M1
Комментарии:
1. Я использовал ваш ответ для простоты, поскольку мне было легче понять. Спасибо.
Ответ №2:
Хорошо, позвольте мне посмотреть, может ли это вам помочь:
select s_no, bucket_window, ts, c as calls from (
select s_no, ts, bucket_window,
max(ts) over (partition by s_no, bucket_window) maxts,
case
when ts=max(ts) over (partition by s_no, bucket_window)
then calls
end c
from (
select m.*,
datepart(hour, m.ts)*2 floor(datepart(minute, m.ts)/30) bucket_window
from mytable1 m
where m.ts >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
) x
) y
where ts=maxts;
Я начал с вашего первоначального запроса, но попытался обойтись без необходимости снова присоединяться к mytable1. Я не знаю синтаксиса SQL Server, но я протестировал его на rextester, и он говорит, что он должен работать.
Вот пример, который я подготовил на rext tester: я ввел все три доступных решения, и все они, похоже, дают ответ, который вы просили.
Поскольку я не знаю синтаксиса SQL Server, я использовал стандартную аналитическую функцию SQL, которую я мог бы использовать также в Teradata или Oracle для изменения вашего запроса.
Комментарии:
1. Он не выдает никаких синтаксических ошибок, но каким-то образом не выводит ни одной строки в результате.
2. Мой плохой, это так. Спасибо.
Ответ №3:
Используйте top(1) со связями .. порядок по row_number()
select top(1) with ties m.s_no, datepart(hour, m.ts)*2 floor(datepart(minute, m.ts)/30) bucket_window, ts, calls
from mytable1 m
where m.ts >= DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
order by row_number() over (partition by m.s_no, datepart(hour, m.ts)*2 floor(datepart(minute, m.ts)/30)
order by ts desc);