Transactions within an hour 一小时内的交易

huangapple go评论54阅读模式
英文:

Transactions within an hour

问题

以下是您要翻译的SQL查询部分:

-- 获取下一行作为列
with nxttab as (
	select 
	timestamp, LEAD(timestamp, 1) over(PARTITION by sender order by timestamp) as nxttimestamp, sender FROM Transactions
), 
-- 获取所有在60分钟内的交易
difftab as (
	select timestamp, nxttimestamp, sender FROM  nxttab where (julianday(nxttimestamp) - julianday(timestamp))*24*60 < 60
), 
-- 获取当前交易 - 上一个下一笔交易的差异
endpoints as (
	select  julianday(timestamp) - julianday(LAG(nxttimestamp,1) OVER ( PARTITION BY sender order by timestamp )) as diffnextday, * from difftab
),
-- 用1标记新交易的开始和所有连续交易的0
intervals as (
	select CASE WHEN diffnextday != 0 THEN 1 ELSE 0 END as startnew , * from endpoints
),
-- 通过从顶部到当前行的累积和来获取每个交易的分区号
partitions as (
	select SUM(startnew) OVER (PARTITION BY Sender ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as partno, * from intervals
)
-- 按发送者、分区号分组查找最小值、最大值和count(*)+1
select sender, partno, min(timestamp) as start, max(nxttimestamp) as end, count(*)+1 as count from partitions
group by sender, partno order by sender;

请注意,我已经将您的SQL查询翻译成中文,并移除了您的请求中不需要的其他内容。如果您需要进一步的帮助,请随时告诉我。

英文:
timestamp sender
22-05-2022 16:19 Ram
22-05-2022 16:59 Ram
--------- ------
04-06-2022 18:19 Tom
04-06-2022 18:30 Tom
04-06-2022 19:20 Tom
04-06-2022 19:40 Tom
04-06-2022 19:50 Tom
04-06-2022 19:55 Tom
-------------- ------
04-06-2022 21:30 Tom
04-06-2022 21:35 Tom
04-06-2022 21:37 Tom
---------------- ------
04-06-2022 22:41 Tom
04-06-2022 22:45 Tom
---------------- ------
04-06-2022 23:47 Tom
04-06-2022 23:49 Tom
04-06-2022 23:50 Tom
04-06-2022 23:55 Tom
---------------- ------
08-06-2022 08:30 Tom
08-06-2022 09:15 Tom
08-06-2022 09:45 Tom
08-06-2022 09:55 Tom
-------------------- ------
10-06-2022 09:19 John
10-06-2022 09:21 John
---------------- ------
08-08-2022 18:19 Harry
08-08-2022 18:32 Harry
---------------- ------

I have table of timestamps and sender. All consecutive timestamps by the same sender within 60 minutes are considered to be from the same branch like Ram at 16:19 and 16.59
Tom between 18:19 and 19:55.I've delimited the timestamps at same branch by "-------" in the table.

How to write a SQL query so that I can print the start and end of transactions at same branch? I've written the following SQL query using SQLite. I use juliandate to find the date difference. I'm allowed to use only SQL and window functions and CTE not any vendor specific extensions. Except for date part for diff the code should work on MySQL, SQLite, Oracle, SQL Server and DB2.

Can someone tell me how to solve this query in a better and elegant way ?
--- get the next row as a column
with nxttab as (
	select 
	timestamp,LEAD(timestamp,1) over(PARTITION by sender order by timestamp) as nxttimestamp,sender FROM Transactions
), 
--- get all Transactions within 60 minutes
difftab as (
	select timestamp, nxttimestamp,sender FROM  nxttab where (julianday(nxttimestamp) - julianday(timestamp))*24*60 &lt; 60
), 
---  get the difference of current transaction - previous next transaction
endpoints as (
	select  julianday(timestamp) - julianday(LAG(nxttimestamp,1) OVER ( PARTITION BY sender order by timestamp )) as diffnextday,* from difftab
),
--- Mark start of new transaction with 1 and all consecutive transactions with 0
 intervals as (
	select CASE WHEN diffnextday!=0 THEN 1 ELSE 0 END as startnew ,* from endpoints
),
--- Get the partition number of each transaction by cummulative sum from top to current row
 partitions as (
select SUM(startnew) OVER (PARTITION BY Sender ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as partno,* from intervals
)
-- group by sender,partition number find min,max and count(*)+1
select sender,partno,min(timestamp) as start,max(nxttimestamp) as end ,count(*)+1 as count from partitions
group by sender,partno order by sender;

答案1

得分: 1

以下是翻译好的部分:

您可以使用以下查询:

WITH PrevValues AS (
-- 获取前一个和下一个时间戳
    SELECT *,
      prevTime = LAG(timestamp) OVER (PARTITION BY sender ORDER BY timestamp),
      nextTime = LEAD(timestamp) OVER (PARTITION BY sender ORDER BY timestamp)
    FROM Transactions t
),
Filtered AS (
-- 仅包括每个部分的开头和结尾
    SELECT *,
-- 为每个部分的开头计算新的前一个时间
      newPrevTime = LAG(timestamp) OVER (PARTITION BY sender ORDER BY timestamp)
    FROM PrevValues
       -- 是开头
    WHERE prevTime IS NULL
       OR DATEADD(hour, 1, prevTime) &lt; timestamp
       -- 或者是结尾
       OR nextTime IS NULL
       OR DATEADD(hour, -1, nextTime) &gt;= timestamp
)
SELECT
  sender,
  StartTime = newPrevTime,
  EndTime = timestamp
FROM Filtered
-- 仅包括结尾行
WHERE nextTime IS NULL
   OR DATEADD(hour, -1, nextTime) &gt;= timestamp;

db<>fiddle

英文:

You can use the following query

WITH PrevValues AS (
-- get previous and next timestamps
    SELECT *,
      prevTime =  LAG(timestamp) OVER (PARTITION BY sender ORDER BY timestamp),
      nextTime = LEAD(timestamp) OVER (PARTITION BY sender ORDER BY timestamp)
    FROM Transactions t
),
Filtered AS (
-- filter to only include the beginning and end of each section
    SELECT *,
-- calculate a new previous time, for the beginning of each section
      newPrevTime = LAG(timestamp) OVER (PARTITION BY sender ORDER BY timestamp)
    FROM PrevValues
       -- is start
    WHERE prevTime IS NULL
       OR DATEADD(hour, 1, prevTime) &lt; timestamp
       -- or is end
       OR nextTime IS NULL
       OR DATEADD(hour, -1, nextTime) &gt;= timestamp
)
SELECT
  sender,
  StartTime = newPrevTime,
  EndTime = timestamp
FROM Filtered
-- only include the end rows
WHERE nextTime IS NULL
   OR DATEADD(hour, -1, nextTime) &gt;= timestamp;

db<>fiddle

huangapple
  • 本文由 发表于 2023年6月9日 12:39:55
  • 转载请务必保留本文链接:https://go.coder-hub.com/76437254.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定