Создание порядковых номеров с ежечасным сбросом

Я пытаюсь создать уникальный порядковый номер для учетной записи, который сбрасывается через час бездействия. Ниже у меня есть код, который генерирует образцы данных и желаемый результат. Я пробовал использовать lag, lead, row_number, объединять столбцы даты и времени в поле datetime для использования в этих функциях, но у меня это не заработало.

Я работаю в SQL Server 2016

DROP TABLE IF EXISTS #TempFuelPurchase;

CREATE TABLE #TempFuelPurchase (
    Account_Number INT,
    FuelPurchase_Date DATE,
    Fuel_TOD TIME
);

INSERT INTO #TempFuelPurchase (Account_Number, FuelPurchase_Date, Fuel_TOD)
VALUES
    (19, '2024-04-03', '07:02:02 AM'),
    (19, '2024-04-03', '07:02:41 AM'),
    (19, '2024-04-03', '02:58:49 PM'),
    (19, '2024-04-03', '07:58:49 PM'),
    (19, '2024-04-05', '02:58:49 PM'),
    (19, '2024-04-05', '02:59:31 PM'),
    (19, '2024-04-17', '11:56:13 PM'),
    (20, '2024-04-17', '11:59:13 PM'),
    (19, '2024-04-18', '12:15:13 AM'),
    (19, '2024-04-18', '02:56:13 PM'),
    (20, '2024-04-18', '07:41:55 AM'),
    (20, '2024-04-18', '07:41:55 PM'),
    (20, '2024-04-18', '07:56:55 PM'),
    (19, '2024-04-19', '07:41:55 AM'),
    (19, '2024-04-19', '07:42:20 AM');



DROP TABLE IF EXISTS #DesiredOutput;

CREATE TABLE #DesiredOutput (
    Account_Number INT,
    FuelPurchase_Date DATE,
    Fuel_TOD TIME,
    Seq Int
);

INSERT INTO #DesiredOutput (Account_Number, FuelPurchase_Date, Fuel_TOD, Seq)
VALUES
    (19, '2024-04-03', '07:02:02 AM',1),
    (19, '2024-04-03', '07:02:41 AM',2),
    (19, '2024-04-03', '02:58:49 PM',1),
    (19, '2024-04-03', '07:58:49 PM',1),
    (19, '2024-04-05', '02:58:49 PM',1),
    (19, '2024-04-05', '02:59:31 PM',2),
    (19, '2024-04-17', '11:56:13 PM',1),
    (20, '2024-04-17', '11:59:13 PM',1),
    (19, '2024-04-18', '12:15:13 AM',2),
    (19, '2024-04-18', '02:56:13 PM',1),
    (20, '2024-04-18', '07:41:55 AM',1),
    (20, '2024-04-18', '07:41:55 PM',1),
    (20, '2024-04-18', '07:56:55 PM',2),
    (19, '2024-04-19', '07:41:55 AM',1),
    (19, '2024-04-19', '07:42:20 AM',2);

спасибо за предоставление полных тестовых данных!

— 25.04.2024 00:41

sql sql-server sequence window-functions sql-server-2016

24.04.2024 18:48

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Я думаю, что этот первый метод может создать немного более дешевый план, поскольку он использует на одну операцию сортировки меньше. Однако второе решение, вероятно, более распространено:

with dataX as (
    select *,
        -- convert to a combined datetime that can be compared easily
        cast(FuelPurchase_Date as datetime) + cast(Fuel_TOD as datetime) as Fuel_DT
    from #TempFuelPurchase
), dataY as (
    select *,
        row_number() over (partition by Account_Number order by Fuel_DT) as rn,
        -- make sure first row gets a dummy value that will create a break
        lag(Fuel_DT, 1, dateadd(day, -1, Fuel_DT)) over (partition by Account_Number order by Fuel_DT) as last_DT
    from dataX
), dataZ as (
    select *,
        -- resolution is whole seconds, tag breaks with the row number
        case when datediff(second, last_DT, Fuel_DT) > 3600 then rn end as brk
    from dataY
)
select Account_Number, FuelPurchase_Date, Fuel_TOD,
    -- adjust count by deducting row number of the previous break
    rn + 1 - max(brk) over (partition by Account_Number order by Fuel_DT) as Seq
from dataZ;

или

with dataX as (
    select *,
        cast(FuelPurchase_Date as datetime) + cast(Fuel_TOD as datetime) as Fuel_DT
    from #TempFuelPurchase
), dataY as (
    select *,
        lag(Fuel_DT, 1, dateadd(day, -1, Fuel_DT)) over (partition by Account_Number order by Fuel_DT) as last_DT
    from dataX
), dataZ as (
    select *,
        -- rather than keeping row numbers, tag each row as part of a group
        sum(
            case when datediff(second, last_DT, Fuel_DT) > 3600 then 1 end
           ) over (partition by Account_Number order by Fuel_DT) as grp
    from dataY
)
select Account_Number, FuelPurchase_Date, Fuel_TOD,
    row_number() over (partition by Account_Number, grp order by Fuel_DT) as Seq
from dataZ;

https://dbfiddle.uk/x82HgZuL

25.04.2024 00:58