SQL для расчета разницы с 1-го дня месяца до указанной даты

Я работаю над запросом sql, чтобы получить различия. У меня есть таблица, содержащая показания, метку времени чтения, идентификатор. Моя конечная цель — получить три отличия. 1. Разница между показаниями за день до следующего дня, 2. Разница между показаниями за 7 дней до значения метки времени, 3. Разница между показаниями, начиная с 1-го числа месяца и до каждой указанной даты.

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

ожидаемый результат: чтение 1 ноября — 1000, 2 ноября — 1020 и 3 ноября — 1050, разница 2 ноября должна быть 20, а 3 ноября — 50.

Если данных за первый день месяца нет, берутся наименьшие данные за доступную дату. Например, сентябрь имеет только от 24 сентября, поэтому возьмите показания с 24 сентября.

Ниже приведен пример таблицы.

+----+-----------+---------+----------------+----------------+-----------------+
| id | timestamp | Reading | 1DayDifference | 7DayDifference | monthDifference |
+----+-----------+---------+----------------+----------------+-----------------+
| A1 | 11/20/18  |   44182 |              0 |            300 |             541 |
| A1 | 11/19/18  |   44182 |              0 |            338 |             541 |
| A1 | 11/18/18  |   44182 |              0 |            338 |             541 |
| A1 | 11/17/18  |   44182 |             38 |            338 |             541 |
| A1 | 11/16/18  |   44144 |            197 |            300 |             503 |
| A1 | 11/15/18  |   43947 |             26 |            103 |                 |
| A1 | 11/14/18  |   43921 |             39 |            158 |                 |
| A1 | 11/13/18  |   43882 |             38 |            158 |                 |
| A1 | 11/12/18  |   43844 |              0 |            120 |                 |
| A1 | 11/11/18  |   43844 |              0 |            120 |                 |
| A1 | 11/10/18  |   43844 |              0 |            160 |                 |
| A1 | 11/09/18  |   43844 |              0 |            203 |                 |
| A1 | 11/08/18  |   43844 |             81 |            241 |                 |
| A1 | 11/06/18  |   43763 |             39 |            198 |                 |
| A1 | 11/05/18  |   43724 |              0 |            198 |                 |
| A1 | 11/04/18  |   43724 |              0 |            198 |                 |
| A1 | 11/03/18  |   43724 |             40 |            198 |                 |
| A1 | 11/02/18  |   43684 |             43 |            199 |                 |
| A1 | 11/01/18  |   43641 |             38 |            194 |                 |
| A1 | 10/31/18  |   43603 |             38 |            275 |             237 |
| A1 | 10/30/18  |   43565 |             39 |            317 |                 |
| A1 | 10/29/18  |   43526 |              0 |            317 |                 |
| A1 | 10/28/18  |   43526 |              0 |            317 |                 |
| A1 | 10/27/18  |   43526 |             41 |            317 |                 |
| A1 | 10/26/18  |   43485 |             38 |            276 |                 |
| A1 | 10/25/18  |   43447 |            119 |            238 |                 |
| A1 | 10/24/18  |   43328 |             80 |            119 |                 |
+----+-----------+---------+----------------+----------------+-----------------+

SQL, который я использовал для первых двух типов.

SELECT  id,
        timestamp,
        Reading,
        Reading - lead(Reading,1,0) OVER( partition BY [id] ORDER BY timestamp desc) [OneDayDifference],
        Reading - lead(Reading,7,0) OVER( partition BY [id] ORDER BY timestamp desc) [SevDayDifference]
FROM    [dbo].[test_example]     s
ORDER BY id, timestamp desc

Ниже приведен скрипт для генерации вышеуказанных данных.

CREATE TABLE [dbo].[test_Example](
    [id] [nvarchar](50) NOT NULL,
    [timestamp] [datetime2](7) NOT NULL,
    [reading] [int] NOT NULL,
    [OneDayDifference] [int] NOT NULL,
    [SevDayDifference] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-19T00:01:38.0000000' AS DateTime2), 44182, 0, 338)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-18T00:01:44.0000000' AS DateTime2), 44182, 0, 338)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-17T00:01:35.0000000' AS DateTime2), 44182, 38, 338)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-16T00:01:39.0000000' AS DateTime2), 44144, 197, 300)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-15T00:01:47.0000000' AS DateTime2), 43947, 26, 103)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-14T00:01:40.0000000' AS DateTime2), 43921, 39, 158)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-13T00:01:38.0000000' AS DateTime2), 43882, 38, 158)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-12T00:02:39.0000000' AS DateTime2), 43844, 0, 120)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-11T00:01:37.0000000' AS DateTime2), 43844, 0, 120)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-10T00:01:37.0000000' AS DateTime2), 43844, 0, 160)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-09T00:01:37.0000000' AS DateTime2), 43844, 0, 203)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-08T00:01:46.0000000' AS DateTime2), 43844, 81, 241)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-06T00:01:36.0000000' AS DateTime2), 43763, 39, 198)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-05T00:02:27.0000000' AS DateTime2), 43724, 0, 198)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-04T00:01:37.0000000' AS DateTime2), 43724, 0, 198)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-03T00:01:48.0000000' AS DateTime2), 43724, 40, 198)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-02T00:01:33.0000000' AS DateTime2), 43684, 43, 199)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-11-01T00:01:41.0000000' AS DateTime2), 43641, 38, 194)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-31T00:01:32.0000000' AS DateTime2), 43603, 38, 275)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-30T00:01:34.0000000' AS DateTime2), 43565, 39, 43565)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-29T00:02:45.0000000' AS DateTime2), 43526, 0, 43526)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-28T00:01:43.0000000' AS DateTime2), 43526, 0, 43526)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-27T00:01:31.0000000' AS DateTime2), 43526, 41, 43526)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-26T00:01:30.0000000' AS DateTime2), 43485, 38, 43485)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-25T00:01:35.0000000' AS DateTime2), 43447, 119, 43447)
GO
INSERT [dbo].[test_Example] ([id], [timestamp], [reading], [OneDayDifference], [SevDayDifference]) VALUES (N'A1', CAST(N'2018-10-24T00:01:43.0000000' AS DateTime2), 43328, 43328, 43328)
GO

sql sql-server tsql azure-sqldw
person gopi nath    schedule 20.11.2018    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Чтобы найти первое число месяца, нужно просмотреть переменное количество строк назад, поэтому вместо LEAD() или LAG() вы можете использовать коррелированный подзапрос в apply. Обратите внимание, что, поскольку вы оглядываетесь назад, я предпочитаю использовать LAG() вместо обратного порядка временной метки и LEAD(), однако любой из них дает тот же результат.

nb: этот подзапрос найдет самую раннюю метку времени в любом месяце, если это нежелательно, добавьте and t.timestamp < dateadd(dd,1,dateadd(mm,datediff(mm,0,s.timestamp),0)) в предложение where.

SELECT
    id
  , timestamp
  , Reading
  , Reading - LAG( Reading, 1, 0 ) OVER (PARTITION BY [id] ORDER BY timestamp) [OneDayDifference]
  , Reading - LAG( Reading, 7, 0 ) OVER (PARTITION BY [id] ORDER BY timestamp) [SevDayDifference]
  , reading - oa.prev_reading [ThisMonthDiff]
FROM [dbo].[test_example] s
outer apply (
    select top(1) t.reading prev_reading
    from [dbo].[test_example] t
    where s.id = t.id
    and t.timestamp >= dateadd(mm,datediff(mm,0,s.timestamp),0)
       -- and t.timestamp < dateadd(dd,1,dateadd(mm,datediff(mm,0,s.timestamp),0))
    order by t.timestamp
    ) oa
ORDER BY
    id
  , timestamp DESC
;

Результат:

+----+----+------------+---------+------------------+------------------+---------------+
|    | id | timestamp  | Reading | OneDayDifference | SevDayDifference | ThisMonthDiff |
+----+----+------------+---------+------------------+------------------+---------------+
|  1 | A1 | 2018-11-19 |   44182 |                0 |              338 |           541 |
|  2 | A1 | 2018-11-18 |   44182 |                0 |              338 |           541 |
|  3 | A1 | 2018-11-17 |   44182 |               38 |              338 |           541 |
|  4 | A1 | 2018-11-16 |   44144 |              197 |              300 |           503 |
|  5 | A1 | 2018-11-15 |   43947 |               26 |              103 |           306 |
|  6 | A1 | 2018-11-14 |   43921 |               39 |              158 |           280 |
|  7 | A1 | 2018-11-13 |   43882 |               38 |              158 |           241 |
|  8 | A1 | 2018-11-12 |   43844 |                0 |              120 |           203 |
|  9 | A1 | 2018-11-11 |   43844 |                0 |              120 |           203 |
| 10 | A1 | 2018-11-10 |   43844 |                0 |              160 |           203 |
| 11 | A1 | 2018-11-09 |   43844 |                0 |              203 |           203 |
| 12 | A1 | 2018-11-08 |   43844 |               81 |              241 |           203 |
| 13 | A1 | 2018-11-06 |   43763 |               39 |              198 |           122 |
| 14 | A1 | 2018-11-05 |   43724 |                0 |              198 |            83 |
| 15 | A1 | 2018-11-04 |   43724 |                0 |              198 |            83 |
| 16 | A1 | 2018-11-03 |   43724 |               40 |              198 |            83 |
| 17 | A1 | 2018-11-02 |   43684 |               43 |              199 |            43 |
| 18 | A1 | 2018-11-01 |   43641 |               38 |              194 |             0 |
| 19 | A1 | 2018-10-31 |   43603 |               38 |              275 |           275 |
| 20 | A1 | 2018-10-30 |   43565 |               39 |            43565 |           237 |
| 21 | A1 | 2018-10-29 |   43526 |                0 |            43526 |           198 |
| 22 | A1 | 2018-10-28 |   43526 |                0 |            43526 |           198 |
| 23 | A1 | 2018-10-27 |   43526 |               41 |            43526 |           198 |
| 24 | A1 | 2018-10-26 |   43485 |               38 |            43485 |           157 |
| 25 | A1 | 2018-10-25 |   43447 |              119 |            43447 |           119 |
| 26 | A1 | 2018-10-24 |   43328 |            43328 |            43328 |             0 |
+----+----+------------+---------+------------------+------------------+---------------+

Выше я использовал outer apply, который действует как внешнее соединение (если соответствующий результат не найден, исходная строка все еще возвращается). Если в этом нет необходимости, используйте вместо этого cross apply.

Редактировать

SELECT
    id
  , format(timestamp, 'yyyy-MM-dd') [timestamp]
  , Reading
  , COALESCE(Reading - LAG( Reading, 1) OVER (PARTITION BY [id] ORDER BY timestamp),0) [OneDayDifference]
  , COALESCE(Reading - LAG( Reading, 7) OVER (PARTITION BY [id] ORDER BY timestamp),0) [SevDayDifference]
  , reading - ca.tr [ThisMonthDiff]
FROM [dbo].[test_example] s
cross apply (
    select top(1) t.reading tr
    from [dbo].[test_example] t
    where s.id = t.id
    and t.timestamp >= dateadd(mm,datediff(mm,0,s.timestamp),0)
    order by t.timestamp
    ) ca
ORDER BY
    id
  , timestamp DESC
;

+----+----+------------+---------+------------------+------------------+---------------+
|    | id | timestamp  | Reading | OneDayDifference | SevDayDifference | ThisMonthDiff |
+----+----+------------+---------+------------------+------------------+---------------+
|  1 | A1 | 2018-11-19 |   44182 |                0 |              338 |           541 |
|  2 | A1 | 2018-11-18 |   44182 |                0 |              338 |           541 |
|  3 | A1 | 2018-11-17 |   44182 |               38 |              338 |           541 |

| 18 | A1 | 2018-11-01 |   43641 |               38 |              194 |             0 |
| 19 | A1 | 2018-10-31 |   43603 |               38 |              275 |           275 |
| 20 | A1 | 2018-10-30 |   43565 |               39 |                0 |           237 |
| 21 | A1 | 2018-10-29 |   43526 |                0 |                0 |           198 |
| 22 | A1 | 2018-10-28 |   43526 |                0 |                0 |           198 |
| 23 | A1 | 2018-10-27 |   43526 |               41 |                0 |           198 |
| 24 | A1 | 2018-10-26 |   43485 |               38 |                0 |           157 |
| 25 | A1 | 2018-10-25 |   43447 |              119 |                0 |           119 |
| 26 | A1 | 2018-10-24 |   43328 |                0 |                0 |             0 |
+----+----+------------+---------+------------------+------------------+---------------+
person Paul Maxwell    schedule 21.11.2018
comment
Сладкий, это прекрасно работает. Я вижу, что наша функция Lead() или Lag() для SevenDayDifference ведет себя немного странно. Последние 7 дней чтения отображают те же показания, что и потому, что у них нет значения для сравнения. какие варианты можно сделать? - person gopi nath; 21.11.2018
comment
Удалите значение нуля по умолчанию в функции задержки (без третьего параметра), если оно будет удалено, вместо этого вы получите нули. - person Paul Maxwell; 21.11.2018
comment
к ответу добавлен вариант запроса; удаление значения LAG по умолчанию, равного нулю - person Paul Maxwell; 21.11.2018

arrow_upward
0
arrow_downward

Вместо использования Lead() используйте подзапрос, который получает верхнюю 1 строку для тех же Id, year и month, упорядоченных по timestamp ASC, и вычислите разницу между reading этой строки, возвращаемой подзапросом.

person Tab Alleman    schedule 20.11.2018
comment
Существуют тысячи идентификаторов, и для каждого дня есть записи для каждого идентификатора за последний год. Я не уверен, как этот метод будет работать для других идентификаторов и данных за столько дней. - person gopi nath; 20.11.2018
comment
Он должен работать нормально. Вероятно, вы можете сделать что-то подобное с помощью CROSS APPLY, но производительность должна быть примерно такой же. - person Tab Alleman; 20.11.2018