作為一個 SQL 新手，真的很掙扎，所以我需要根據它們的創建時間將 is_registered 列中的值放入每小時存盤桶中。下面是一個小樣本
| 創立日期 | 已被注冊 |
|---|---|
| 2021-10-28 00:03:12.240 | 1 |
| 2021-10-28 00:09:16.221 | 1 |
| 2021-10-28 00:12:23.234 | 1 |
| 2021-10-29 00:03:19.240 | 1 |
| 2021-10-29 00:48:12:190 | 1 |
| 2021-10-29 01:09:36:129 | 1 |
| 2021-10-29 01:29:29:120 | 1 |
我想要實作的結果（使用完整資料集）如下（一天中每小時的桶
| 日期 | 小時桶 | 每桶總計 |
|---|---|---|
| 2021-10-28 | 00:00-01:00 | 289 |
| 2021-10-28 | 01:00-02:00 | 876 |
| -------- | -------------- | ------------- |
| 2021-10-29 | 00:00-01:00 | 190 |
| 2021-10-29 | 01:00-02:00 | 309 |
等等。
希望提供足夠的資訊，任何幫助將不勝感激，謝謝
uj5u.com熱心網友回復：
嘗試以下方式
--Create table script
CREATE TABLE [dbo].[Table_4](
[creationdate] [datetime] NULL,
[isreg] [int] NULL
) ON [PRIMARY]
GO
--Sample data
insert into table_4 values (getdate(),1)
insert into table_4 values (getdate()-1,1)
go
-- query
WITH report(N) AS(
SELECT TOP(23) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM sys.columns
)
,hourly(creationdate) AS(
SELECT DATEADD(HOUR, t.N, d.creationdate)
FROM report t
CROSS JOIN(
SELECT DISTINCT DATEADD(DD, DATEDIFF(DD, 0, creationdate), 0) AS creationdate FROM table_4
) d
)
SELECT
convert(date, h.creationdate) as [Creation date],
convert(varchar(5), DATEPART(HOUR, h.creationdate)-1) ' - ' convert(varchar(5),DATEPART(HOUR, h.creationdate)) as [Hour Bucket],
[Total in each bucket] = ISNULL(t.isreg, 0)
FROM hourly h
LEFT JOIN(
SELECT
creationdate = DATEADD(HOUR, DATEPART(HOUR, creationdate) ,DATEADD(DD, DATEDIFF(DD, 0, creationdate), 0)),
isreg = COUNT(*)
FROM table_4
GROUP BY DATEADD(DD, DATEDIFF(DD, 0, creationdate), 0), DATEPART(HOUR, creationdate)
)t
ON t.creationdate = h.creationdate
order by h.creationdate
參考鏈接
uj5u.com熱心網友回復：
使用這個 tvf 可能更易于維護： DateRange TVF
WITH Ranges
AS
(
SELECT [Value] AS date_from
,LEAD([Value]) OVER (ORDER BY [Value]) AS date_to
,CAST([Value] AS date) AS [Date]
,CONVERT(char(5), [Value], 14) '-'
CONVERT(char(5), LEAD([Value]) OVER (ORDER BY [Value]), 14) AS Hour_Bucket
FROM dbo.DateRange('2021-10-28', '2021-10-30', 'hh', 1)
)
SELECT R.[Date], R.Hour_Bucket
,COUNT(T.is_registered) AS Total_Registered
FROM Ranges R
LEFT JOIN YourTable T
ON T.creation_date >= R.date_from
AND T.creation_date < R.date_to
AND T.is_registered = 1
WHERE R.date_to IS NOT NULL
GROUP BY R.[Date], R.Hour_Bucket
ORDER BY [Date], Hour_Bucket;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/343284.html
標籤：sql sql-server 约会时间 桶
上一篇：如何根據日期時間修剪資料框？