Sql 创建每小时重置的序列号

发布于04月25日

我正在try 创建一个帐户独有的序列号，并在无活动一小时后重置.下面我有生成示例数据和所需输出的代码.我try 过执行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);

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 或der 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 或der 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 或der 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 或der 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 或der by Fuel_DT) as grp from dataY ) select Account_Number, FuelPurchase_Date, Fuel_TOD, row_number() over (partition by Account_Number, grp 或der by Fuel_DT) as Seq from dataZ;

Sql 创建每小时重置的序列号

推荐答案

Sql相关问答推荐

当编号和版本的唯一状态更改时报告

Oracle SQL根据列中的条件 Select 最大记录数

我怎样才能得到列值对应的最大值在另一个？

我可以在SQL的IN子句中使用比子查询包含的值更少的值吗？

两个不同星期的销售额，不加成一行

在Netezza SQL中将字符DataType转换为整型DataType

将一个数组反嵌套到另外两个数组SQL中(Athena/presto)

组合2个分区表的postgres视图的执行计划正在访问所有分区

Postgres jsonpath运算符的变量替换，如_regex？

无法访问级联删除导致的触发器中已删除的外键记录

如何使用jsonn_populate_record()插入到包含IDENTITY列的表中

在Postgres，什么是；.USSTZ；在'；YYYY-MM-DD；T；HH24：MI：SS.USSTZ'；？

为什么SQL in中的空子查询有时被视为null

将具有嵌套 XML 的列转换为 SQL 中的表格格式？

给定 3 个键列，从一个表中 Select 另一表中不存在的所有数据

SQL 如何根据当前事件和下一个事件确定操作的持续时间？

有没有办法在雅典娜中将字符串转换为 int ？

避免在SQL中使用具有相同条件的多个子查询

使用对 nvarchar 列的多个 LIKE 操作优化 SQL 查询

在 PostgreSQL 中，如何将数组中的每个元素用作另一个表中的键？