英文:
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 < 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) < timestamp
-- 或者是结尾
OR nextTime IS NULL
OR DATEADD(hour, -1, nextTime) >= timestamp
)
SELECT
sender,
StartTime = newPrevTime,
EndTime = timestamp
FROM Filtered
-- 仅包括结尾行
WHERE nextTime IS NULL
OR DATEADD(hour, -1, nextTime) >= timestamp;
英文:
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) < timestamp
-- or is end
OR nextTime IS NULL
OR DATEADD(hour, -1, nextTime) >= timestamp
)
SELECT
sender,
StartTime = newPrevTime,
EndTime = timestamp
FROM Filtered
-- only include the end rows
WHERE nextTime IS NULL
OR DATEADD(hour, -1, nextTime) >= timestamp;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论