时间戳

首页 > 解决方案 > T-SQL 运行总和

sql - T-SQL 运行总和

问题描述

考虑以下形状的表格(wallet, client, date)， wherewallet和clientare strings，并且date是直到今天的任何日期。假设一个人想要获得今年每个月的唯一客户和客户的运行总和，按钱包和月份分组。我已经尝试了几件事，包括分区，但我就是无法让它正常工作。

结果将是这样的：

+------------+---------+------------+---------+
|    Wallet  |  Unique |  Count     | Month   |
+------------+---------+------------+---------+
| Wallet1    | 2       |   5        |   1     | - For < 01022019
+------------+---------+------------+---------+
| Wallet1    | 3       |   7        |   2     | - For < 01032019
+------------+---------+------------+---------+

等等。我尝试了很多事情，甚至是分区，但我失败了。我只设法把它变成了几个小查询，都在日期下面使用 WHERE，这绝对是糟糕的，.eg

INSERT INTO 
    tmp_tbl_filtered
SELECT
    [Wallet],
    COUNT(DISTINCT [Client]) AS [unique],
    COUNT([Client]) AS [count],
    1 AS [month]
FROM
    tmp_tbl
WHERE [Date] < '20190201'
GROUP BY  [Wallet]
ORDER BY [Wallet]

INSERT INTO 
    tmp_tbl_filtered
SELECT
    [Wallet],
    COUNT(DISTINCT [Client]) AS [unique],
    COUNT([Client]) AS [count],
    2 AS [month]
FROM
    tmp_tbl
WHERE [Date] < '20190301'
GROUP BY  [Wallet]
ORDER BY [Wallet]

标签： sqlsql-server-2008

解决方案

如果我做对了

SELECT [Wallet], [month]
   , SUM([unique]) OVER(PARTITION BY [Wallet] ORDER BY [month]) [unique_rt]
   , SUM([count]) OVER(PARTITION BY [Wallet] ORDER BY [month]) [count_rt]
FROM (
    --  group data of the current year 2019 by wallet and month
    SELECT 
        [Wallet],
        DATEPART(month,[Date]) AS [month],
        COUNT(DISTINCT [Client]) AS [unique],
        COUNT([Client]) AS [count]
    FROM
        tmp_tbl
    WHERE [Date] >= '20190101'
    GROUP BY [Wallet], DATEPART(month,[Date])
) t
ORDER BY [Wallet], [month]

推荐阅读