我需要撰寫一個 SQL 查詢，它每 20 分鐘記錄一個 datetime 列塊中的一條記錄，并由另一個欄位磁區。
例如，如果我要運行一個查詢，從我的表中提取特定時間范圍內的所有資料，我可能會看到以下內容。如果我要對此資料執行 COUNT(1)，我將得到 29 的結果。
我想要的是每 20 分鐘只能計算一條記錄，從 MIN(ActionTime) 開始。如果您查看以下內容，我只想回傳 4 的計數。
我有以下示例
CREATE TABLE #tmp (ID INT, ActionTime DATETIME)
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:21:11')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:21:12')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:21:23')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:21:23')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:39:13')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:39:15')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:39:18')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:41:04')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:41:08')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:42:29')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:42:31')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:43:47')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:43:55')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:44:44')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:44:47')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:51:21')
;
WITH data
AS (SELECT t.*,
ROW_NUMBER() OVER (ORDER BY t.ActionTime) rn
FROM #tmp t
),
cte
AS (SELECT d.*,
d.ActionTime AS first_search_time
FROM data d
WHERE rn = 1
UNION ALL
SELECT d.*,
CASE
WHEN d.ActionTime > DATEADD(MINUTE, 20, c.first_search_time) THEN
d.ActionTime
ELSE
c.first_search_time
END
FROM cte c
INNER JOIN data d
ON d.rn = c.rn 1
)
SELECT c.*,
DENSE_RANK() OVER (PARTITION BY c.ID ORDER BY first_search_time) grp
INTO #tmp_dense_rank
FROM cte c
OPTION (MAXRECURSION 0);
SELECT ID, COUNT(DISTINCT grp) AS Logins
FROM #tmp_dense_rank
GROUP BY ID
DROP TABLE #tmp
DROP TABLE #tmp_dense_rank
If I run this example it works as expected and returns a count of 4. However, when I extend the date range to search for say a months worth of data, it is taking forever to run and the estimated execution plan is saying there are 87 million rows. A straight select for the month only returns 40500 rows. So is the CTE doing something wrong recursively or is there a Cartesian issue?
Sorry, this wasn't really the easiest to explain or demonstrate but I hope I have explained it enough for you to understand what I need. Its driving me mad. Also, if there is better way of doing this then please let me know.
uj5u.com熱心網友回復：
我的方法是生成一個每個 20 分鐘寬度的休息時間串列，然后獲取每個休息時間的最小操作時間。
CREATE TABLE #tmp (ID INT, ActionTime DATETIME)
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:21:11')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:21:12')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:21:23')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:21:23')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:39:13')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:39:15')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:39:18')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:41:04')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:41:08')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:42:29')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:42:31')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:43:47')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:43:55')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:44:44')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:44:47')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:51:21');
--generate a list of the 20-minute breaks
--DROP TABLE #breaks;
CREATE TABLE #breaks (break_id int, break_from_ts DATETIME, break_to_ts DATETIME);
DECLARE @from int = 1;
DECLARE @to int = 10000;
DECLARE @minActionTime datetime = (SELECT MIN(ActionTime) AS min_ActionTime FROM #tmp);
WITH intlist
AS (SELECT num = @from
UNION ALL
SELECT num 1
FROM intlist
WHERE num 1 <= @to)
INSERT INTO #breaks (break_id, break_from_ts, break_to_ts)
SELECT 1 AS break_id, @minActionTime AS break_from_ts, DATEADD(MINUTE,20,@minActionTime) AS break_to_ts
UNION ALL
SELECT intlist.num 1, DATEADD(MINUTE,20 * intlist.num,@minActionTime) AS break_from_ts, DATEADD(MINUTE,20 * (intlist.num 1),@minActionTime) AS break_to_ts
FROM intlist
OPTION (maxrecursion 0);
--a list of 10001 breaks each 20 minutes in width
SELECT * FROM #breaks;
--get the min ActionTime by break_id etc
SELECT id, COUNT(*) AS logins
FROM (
SELECT x.id, y.break_id, y.break_from_ts, y.break_to_ts, MIN(x.ActionTime) AS min_ActionTime
FROM #tmp x
INNER JOIN #breaks y ON x.ActionTime >= break_from_ts AND x.ActionTime < break_to_ts
GROUP BY x.id, y.break_id, y.break_from_ts, y.break_to_ts
) x
GROUP BY id;
當然，您在該 intlist CTE 中放置的中斷次數完全取決于您。您可能想要計算所考慮的總分鐘數并適當地縮放 CTE。
編輯：作為事后的想法，如果 CTE 太大，您會注意到 CTE 會變慢，因此您可能需要考慮在資料庫中創建一個永久表，該表僅包含很長的整數串列，因此您可以將其用于此目的和其他目的，而不是使用重復的 CTE。
uj5u.com熱心網友回復：
如果我理解正確，那么這應該有效：
CREATE TABLE #tmp (ID INT, ActionTime DATETIME)
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:21:11')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:21:12')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:21:23')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:21:23')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:39:13')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:39:15')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:39:18')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:41:04')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:41:08')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:42:29')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:42:31')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:43:47')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:43:55')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:44:44')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:44:47')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:51:21')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:52:39')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 07:52:48')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 08:47:38')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 08:47:39')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 08:51:25')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 08:51:25')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 08:51:25')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 11:09:00')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 11:09:01')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 11:09:10')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 11:09:10')
INSERT INTO #tmp VALUES (-57267, '02/08/2021 11:09:51')
;
with rolledup as (
SELECT #tmp.ID,
round(
CAST(actiontime - logintimes.firstlogin AS float) * 72.0, 0, 1) as period
from #tmp
inner join (
select ID,
min(actiontime) as firstlogin
from #tmp
group by ID
) logintimes on logintimes.ID = #tmp.id
group by #tmp.ID, round(CAST(actiontime - logintimes.firstlogin AS float) * 72.0,0,1)
)
select ID,
count(1) as Logins
from rolledup
group by ID
drop table #tmp
跑步給了我-57267,4
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/422046.html
標籤：
上一篇：從某個日期開始的周數