计算运行计数 &使用 SQL 在客户之间运行总计 [英] Calculating a running count & running total across customers with SQL

问题描述

我有下表(SQL Server 2012):

I have the following table (SQL Server 2012):

DID - cust id
GID - order id
AMT - order amt
Gf_Date - order date
SC - order reversal amount

我正在尝试按客户计算订单的运行计数和销售额的运行总数，以便我可以为客户实现累计销售额 1,000 美元的时间点分配一个标志.作为第一步，我运行了这个查询:

I'm trying to calculate a running count of orders and a running total of sales by customer so that I can assign a flag to the point in time where a customer achieved cumulative sales of $1,000. As a first step, I've run this query:

Select
  [DID]
, [AMT]
, [Gf_Date]
, COUNT([GID]) OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) [RunningGift_Count]
, SUM([AMT]) OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) [CumlativeTotal]
FROM [dbo].[MCT]
WHERE [SC] is null
ORDER BY [DID]

但我收到错误消息:

消息 102，级别 15，状态 1，第 3 行 'order' 附近的语法不正确

Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'order'

我早些时候发布了这个并粘贴了错误的错误消息.遗憾和道歉.你在上面看到的是我得到的结果.有人评论说这个语法不正确.现在一切正常，有人能告诉我我做错了什么吗?

I posted this earlier with the wrong error message pasted in. Regrets and apologies. What you see above is the result I'm getting. Someone commented that this syntax is incorrect. Now that all is in order, can someone tell me what I'm doing wrong?

谁能帮帮我?到处都找不到解决办法！谢谢！

Can anyone help me out? Can't find a solution anywhere! Thanks!

推荐答案

您应该使用 ROW_NUMBER (link) 而不是 COUNT:

You should use ROW_NUMBER (link) instead of COUNT:

DECLARE @Threshold NUMERIC(19,2)=1000; -- Use the same data type as `[AMT]`'s data type

Select
  [DID]
, [AMT]
, [Gf_Date]
--, COUNT([GID]) OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) [RunningGift_Count]
, ROW_NUMBER() OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) [RunningGift_Count]
, SUM([AMT]) OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) [CumlativeTotal]
, CASE
      WHEN SUM([AMT]) OVER (PARTITION BY [DID] ORDER BY [Gf_Date]) >= @Threshold THEN 1
      ELSE 0
  END IsThresholdPassed
FROM [dbo].[MCT]
WHERE [SC] is null
ORDER BY [DID]

这篇关于计算运行计数 &使用 SQL 在客户之间运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！