所以我有以下兩列，我想在 SQL 中按日期創建一個運行總列 - 這是一個小片段
| 創立日期 | 已被注冊 |
|---|---|
| 2021-03-30 | 1 |
| 2021-03-30 | 1 |
| 2021-03-31 | 1 |
| 2021-03-31 | 1 |
我似乎遇到的問題是日期有時間戳，而 reg 列是位格式，所以我嘗試了以下查詢
with reg as(
select
cast([created_at] as date) as 'date',
sum(cast([is_registered] as int)) as 'sum_of_reg'
FROM [dbo].[Registrations]
group by [created_at]
)
select
[date],
sum_of_reg,
sum(sum_of_reg) over (order by [date]) as 'running total'
FROM reg
group by [date], sum_of_entries
order by [date] asc
但是，這將回傳以下內容：
| 日期 | reg的總和 | 總計 |
|---|---|---|
| 2021-03-30 | 1 | 1 |
| 2021-03-30 | 1 | 1 |
| 2021-03-31 | 2 | 3 |
我想回來
| 日期 | reg的總和 | 總計 |
|---|---|---|
| 2021-03-30 | 2 | 1 |
| 2021-03-31 | 2 | 3 |
它不會將日期組合成一個不同的值，而是兩次顯示相同的日期。
我認為由于時間戳的原因它仍然單獨處理日期，但不確定解決方法
任何建議將不勝感激！
uj5u.com熱心網友回復：
您在regCTE 中有錯誤的分組子句，您需要cast([created_at] as date).
外層group by不是必須的
不幸的是，
OVER子句中的默認視窗（當有 時ORDER BY）是RANGE UNBOUNDED PRECEDING，這很少是人們所期望的。
您必須ROWS UNBOUNDED PRECEDING明確指定。
with reg as(
select
cast([created_at] as date) as [date],
sum(cast([is_registered] as int)) as [sum_of_reg]
FROM [dbo].[Registrations]
group by cast([created_at] as date)
)
select
[date],
sum_of_reg,
sum(sum_of_reg) over (order by [date] ROWS UNBOUNDED PRECEDING) as [running total]
FROM reg
order by [date] asc
Y9ou 甚至可以在一個級別中做到這一點
select
cast([created_at] as date) as [date],
sum(cast([is_registered] as int)) as [sum_of_reg],
sum(sum(cast([is_registered] as int)))
over (order by [date] ROWS UNBOUNDED PRECEDING) as [running total]
FROM [dbo].[Registrations]
group by cast([created_at] as date)
order by [date] asc
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/341125.html
標籤：sql sql-server 查询语句 累积和