sql-server - 累计金额及百分比

Question

我有一个名为 Sales 的表:

id_sale  id_ticket  total  state
-------  ---------  -----  -----
100      100        30     inactive    
101      101        30     active    
102      101        60     active    
103      102        30     active    
104      102        30     active    
105      103        10     active    
106      103        5      active    

我需要的是得到 total 的总和列，当状态处于事件状态时的总和(总计)并将其乘以 x 百分比(我可以这样做)，因此我必须选择它们的总和(所有总和的票)为 <= sum(total) * x percentage 的所有票并将其复制到另一个表(相同的值)

所以如果我有 101 号票的总和 = 90 号票的总和 = 60 号票的总和以及 103 号票的总和 = 15，并且总和(总计)*x 百分比 = 160，查询将只返回所有来自票证 101 和 102 的信息。

整个早上我都在阅读，我发现了一段代码，它以某种方式完成了我需要的事情，但使用的是 id_Sales 而不是 id_ticket:

    select t1.id_sale, t1.total, SUM(t2.total) as sum
    from sales t1
    inner join sales t2 on t1.id_sale >= t2.id_sale
    where t2.state='active'
    group by t1.id_sale, t1.total
    having sum(t2.total)<=(
                           select sum(total)*.65
                           from sales
                           where state='active')
    order by t1.id_sale

我希望收到的是:

id_sale  id_ticket  total  state
-------  ---------  -----  ----- 
101      101        30     active    
102      101        60     active    
103      102        30     active    
104      102        30     active

其中票101和票102之和为<= sum(total)*x

所以 101 和 102 的总和 = 150 和 sum(total)*x = 160(例如)

我怎样才能做我需要的事情，或者我怎样才能编辑这段代码，让它做我正在寻找的事情。

Answer 1

你可以从这样的事情开始:

WITH grouped_and_ranked AS (
  SELECT
    id_ticket,
    ticket_total = SUM(total),
    grand_total = SUM(SUM(total)) OVER (),
    ticket_rnk = ROW_NUMBER() OVER (ORDER BY id_ticket)
  FROM sales
  WHERE state = 'active'
  GROUP BY id_ticket
),
cumulative AS (
  SELECT
    id_ticket,
    ticket_total,
    grand_total,
    ticket_rnk,
    total_so_far = ticket_total
  FROM grouped_and_ranked
  WHERE ticket_rnk = 1
  UNION ALL
  SELECT
    r.id_ticket,
    r.ticket_total,
    r.grand_total,
    r.ticket_rnk,
    total_so_far = c.total_so_far + r.ticket_total 
  FROM grouped_and_ranked r
    INNER JOIN cumulative c ON r.ticket_rnk = c.ticket_rnk + 1
  WHERE r.ticket_total columns
)
SELECT
  s.id_sale,
  s.id_ticket,
  s.total,
  s.state
FROM sales s
INNER JOIN cumulative c ON s.id_ticket = c.id_ticket
;

第一个CTE , grouped_and_ranked，检索有效销售额并按票据和总计计算总计(使用 windowed SUM())。它还为门票分配排名编号，以便稍后在计算运行总数时使用。

下一个 CTE，cumulative，是一个递归 CTE。只要运行总数不大于总计的指定 @percent'age，它就会从以前的结果集中检索行。

主 SELECT 使用最终的工单列表获取详细信息行，方法是将列表连接回 sales 表。

这个查询可以测试at SQL Fiddle .