按月查找客户何时满足或超过请求

Question

我有一个包含客户的表，我想查找客户满足或超过一定数量的请求的月份。

该表customer_id每个请求的时间戳。

我要查找的是客户满足或超过 10000 个请求的月份(或日期(。我试图获得一个运行总数，但这对我不起作用。我把它留在代码中，以防有人知道我该怎么做。

我拥有以下内容：

SELECT 
customer_id
, DATE_TRUNC(CAST(TIMESTAMP_MILLIS(created_timestamp) AS DATE), MONTH) as cMonth
, COUNT(created_timestamp) as searchCount
--     , SUM(COUNT (DISTINCT(created_timestamp))) OVER (ROWS UNBOUNDED PRECEDING) as RunningTotal2
FROM customer_requests.history.all
GROUP BY distributor_id, cMonth
ORDER BY 2 ASC, 1 DESC;

我所追求的代表是这样的。

customer    requests    cMonth       totalRequests
cust1       6000         2017-10-01  6000
cust1       4001         2017-11-01  10001
cust2       4000         2017-10-01  4000
cust2       4000         2017-11-01  8000
cust2       4000         2017-12-01  12000
cust2       3000         2017-12-01  3000
cust2       3000         2017-12-01  6000
cust2       3000         2017-12-01  9000
cust2       3000         2017-12-01  12000

Answer 1

假设SQL Server，请尝试此操作(调整顶部的截止值以获取所需的事务数;现在它查找每个客户的千分之一个事务(。

请注意，这不会返回未超过截止值的客户，并假定每笔交易都有一个唯一的日期(或者，如果日期可以有联系，则会颁发一个连续的 ID 号以中断关系(。

DECLARE @cutoff INT = 1000;
WITH CTE
AS (SELECT customer_id,
transaction_ID,
transaction_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY transaction_date, transaction_ID) AS RN,
COUNT(transaction_ID) OVER (PARTITION BY customer_id) AS TotalTransactions
FROM #test)
SELECT DISTINCT
customer_id,
transaction_date as CutoffTransactionDate,
TotalTransactions
FROM CTE
WHERE RN = @cutoff;

工作原理：

row_number

按照交易的顺序为客户的每笔交易分配一个唯一的顺序标识符。count告诉您一个人进行的交易总数(再次假设每笔交易有一条记录 - 否则您需要单独计算，因为distinct不适用于partition(。

然后，第二个select返回每个客户的第 1,000 行(或您指定的任何行(及其日期，以及该客户的总计。

Answer 2

这是我的解决方案。

SELECT
customerid
,SUM(requests) sumDay
,created_timestamp
FROM yourTable
GROUP BY 
customerid,
created_timestamp
HAVING SUM(requests) >= 10000;

它非常简单。您只需根据需要进行分组，汇总请求并选择符合 HAVING 子句的行。 您可以在此处尝试查询。

Answer 3

如果需要累积总和，可以使用窗口函数。 在标准SQL中，这看起来像：

SELECT customer_id, 
DATE_TRUNC(CAST(TIMESTAMP_MILLIS(created_timestamp) AS DATE), MONTH) as cMonth
COUNT(*) as searchCount,
SUM(COUNT(*)) OVER (ORDER BY MIN(created_timestamp) as runningtotal
FROM customer_requests.history.all
GROUP BY distributor_id, cMonth
ORDER BY 2 ASC, 1 DESC;