每位客户在特定时间段内的累计总额。

huangapple go评论138阅读模式
英文:

Running total for each customer for a certain time period

问题

SELECT 
  strftime('%Y-%m', Payments.date) AS month,
  Customers.customer_name,
  COALESCE(SUM(sum_payment), 'payments does not exist') AS running_total
FROM Customers
CROSS JOIN (SELECT DISTINCT strftime('%Y-%m', date) AS month FROM Payments WHERE strftime('%Y', date) = '2020') AS months
LEFT JOIN Payments ON Customers.customer = Payments.customer AND strftime('%Y-%m', Payments.date) = months.month
GROUP BY months.month, Customers.customer_name
ORDER BY months.month, Customers.customer_name;
英文:

I have two tables

Customers

customer customer_name
1 Headley Quincey
2 Andie Smith
3 Sarah Johnson
4 Ernest Forrest

Payments

payment_id date customer sum_payment
1 2010-01-02 2 200
2 2011-06-06 3 500
3 2020-01-01 1 700
4 2020-02-01 1 100
5 2020-03-10 2 400
6 2020-04-08 3 500
7 2020-07-14 4 800
8 2020-09-05 1 1000

I need to write a query that returns all the months of 2020, the customer name and the running total for each customer. If the buyer does not have a payment in a certain month, then display "payments does not exist".

I suppose I need use CASE, but I dont know how to implement query, and how to implement the running total, and display months of 2020.

My attempt:

SELECT customer_name, SUM(sum_payment)
FROM Customers  INNER JOIN 
Payments ON Customers.customer=Payments.customer
GROUP BY customer_name;

答案1

得分: 2

以下是翻译的内容:

需要为每个客户按照每个2020年的月份返回一条记录,所以你需要:

  • 在所有可能的月份(1-12)和所有客户之间进行交叉连接 CROSS JOIN
  • 在所有这些配对之间使用你的支付表进行左连接 LEFT JOIN
  • 运行总和,你可以使用 SUM 窗口函数来实现

你可以通过进行 11 次 UNION ALL 操作来生成月份,或者使用递归查询,从第一个月开始,每次迭代增加一个月,直到 12 个月(我个人觉得后者更加优雅)。

WITH RECURSIVE months AS (
    SELECT 1 AS month
    UNION ALL
    SELECT month + 1 FROM months WHERE month < 12
)
SELECT c.customer_name,
       m.month,
       SUM(p.sum_payment, 0) OVER(PARTITION BY c.customer 
                                  ORDER BY m.month) AS sum_payment
FROM months m
CROSS JOIN customers c
LEFT JOIN payments p
       ON m.month = MONTH(p.date_)
      AND c.customer = p.customer
      AND YEAR(p.date_) = 2020

在你的查询中,你可以跳过最后的 ORDER BY 子句,它仅用于可视化目的。

查看演示 这里

注意: "如果买家在某个月没有付款,那么显示“付款不存在”。你无法这样做。每个字段只与一个类型相关联。你需要决定它是一个字符串还是一个整数。我建议你将其保留为整数,因为这是该字段应该存储的内容。如果你不想要 NULL 值,而更喜欢在其位置使用零,你可以将 SUM(p.sum_payment) 更改为 SUM(COALESCE(p.sum_payment, 0))

英文:

You need to return a record for every customer by every 2020 month, so you need

  • a CROSS JOIN between all potential month (1-12) with all customers
  • a LEFT JOIN between all these pairs with your payments table
  • the running sum, for which you can use the SUM window function

You can generate your months either by making a 11 UNION ALL operations of months, or with a recursive query that starts from month 1 and adds one month at each iteration, stopping at 12 (I personally find the latter one more elegant).

WITH RECURSIVE months AS (
    SELECT 1 AS month
    UNION ALL
    SELECT month + 1 FROM months WHERE month &lt; 12
)
SELECT c.customer_name,
       m.month,
       SUM(p.sum_payment, 0) OVER(PARTITION BY c.customer 
                                  ORDER     BY m.month) AS sum_payment
FROM       months    m
CROSS JOIN customers c
LEFT JOIN  payments  p
       ON m.month = MONTH(p.date_)
      AND c.customer = p.customer
      AND YEAR(p.date_) = 2020
ORDER BY c.customer_name, m.month

You can skip the last ORDER BY clause in your query: it's just for visualization purposes.

Check the demo here.

Note: "If the buyer does not have a payment in a certain month, then display "payments does not exist".". You can't do it. Each field is associated with one and one type only. You either need to decide whether it's a string or an integer. I'd recommend you to leave it as integer, as it is what that field is supposed to store. If you don't want the NULL values and prefer zeroes in place, you can change SUM(p.sum_payment) with SUM(COALESCE(p.sum_payment, 0)).

答案2

得分: 1

以下是您要翻译的内容:

您可以首先使用递归的 cte 生成月份,然后将支付和客户连接到它上面,计算每个客户和月份的总和。如果对于给定的月份-客户对没有总和存在,则可以包括 &#39;payments do not exists&#39;

with recursive cte(d) as (
   select cast('2020-01-01' as date)
   union all
   select c.d + interval 1 month from cte c where extract(month from c.d) < 12
)
select t.d, c.customer_name, case when t.s is null then 'payments do not exists' else t.s end 
from (
    select c.d, c1.customer, sum(p.sum_payment) s from cte c 
    cross join customers c1 
    left join payments p on p.customer = c1.customer and year(c.d) = year(p.date) and month(c.d) = month(p.date)
    group by c.d, c1.customer) t
join customers c on c.customer = t.customer
order by t.customer, t.d

查看 fiddle

英文:

You can first generate the months with a recursive cte, and then join the payments and customers onto it, computing the sum for each customer and month. If no sum exists for a given month-customer pair, &#39;payments do not exists&#39; can be included instead:

with recursive cte(d) as (
   select cast(&#39;2020-01-01&#39; as date)
   union all
   select c.d + interval 1 month from cte c where extract(month from c.d) &lt; 12
)
select t.d, c.customer_name, case when t.s is null then &#39;payments do not exists&#39; else t.s end 
from (
    select c.d, c1.customer, sum(p.sum_payment) s from cte c 
    cross join customers c1 
    left join payments p on p.customer = c1.customer and year(c.d) = year(p.date) and month(c.d) = month(p.date)
    group by c.d, c1.customer) t
join customers c on c.customer = t.customer
order by t.customer, t.d

See fiddle

huangapple
  • 本文由 发表于 2023年5月21日 07:14:52
  • 转载请务必保留本文链接:https://go.coder-hub.com/76297688.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定