SQL实现：统计客户日总交易数、分产品日交易数并按日期倒序

需编写SQL查询完成以下两项统计：

• 统计每位客户每日的总交易笔数
• 统计每位客户每日各产品对应的交易笔数

注意：所有查询结果需按照交易日期从新到旧倒序排列

执行以下SQL即可创建测试表并写入样例数据：

CREATE TABLE customers(
   customer_id   INTEGER  NOT NULL PRIMARY KEY 
  ,customer_name VARCHAR(15) NOT NULL
);

INSERT INTO customers(customer_id,customer_name) VALUES (1,'Thomas');
INSERT INTO customers(customer_id,customer_name) VALUES (2,'Raymond');

CREATE TABLE transactions_details(
   transaction_id   INTEGER  NOT NULL PRIMARY KEY 
  ,customer_id      INTEGER  NOT NULL
  ,product          VARCHAR(5) NOT NULL
  ,transaction_date DATE  NOT NULL
);

INSERT INTO transactions_details(transaction_id,customer_id,product,transaction_date) VALUES (1,1,'Milk','2022-08-02');
INSERT INTO transactions_details(transaction_id,customer_id,product,transaction_date) VALUES (2,2,'Milk','2022-08-03');
INSERT INTO transactions_details(transaction_id,customer_id,product,transaction_date) VALUES (3,2,'Eggs','2022-08-03');
INSERT INTO transactions_details(transaction_id,customer_id,product,transaction_date) VALUES (4,1,'Milk','2022-08-02');
INSERT INTO transactions_details(transaction_id,customer_id,product,transaction_date) VALUES (5,1,'Bacon','2022-08-03');
INSERT INTO transactions_details(transaction_id,customer_id,product,transaction_date) VALUES (6,1,'Milk','2022-08-02');

1. 每位客户每日总交易笔数统计 #

按客户ID、交易日期分组，关联客户表获取客户名称，统计每组交易数量即可：

SELECT 
    c.customer_id,
    c.customer_name,
    t.transaction_date,
    COUNT(t.transaction_id) AS total_transaction_count
FROM transactions_details t
LEFT JOIN customers c ON t.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name, t.transaction_date
ORDER BY t.transaction_date DESC;

样例数据下执行返回结果：

2. 每位客户每日各产品交易笔数统计 #

在上述分组逻辑基础上增加产品维度分组，即可得到分产品的统计结果：

SELECT 
    c.customer_id,
    c.customer_name,
    t.transaction_date,
    t.product,
    COUNT(t.transaction_id) AS product_transaction_count
FROM transactions_details t
LEFT JOIN customers c ON t.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name, t.transaction_date, t.product
ORDER BY t.transaction_date DESC;

样例数据下执行返回结果：

补充说明：如果业务要求无交易记录的客户也需要出现在统计结果中，可以调换两表关联顺序，以客户表为主表左连交易表，对空交易记录做0值处理即可。

内容的提问来源于stack exchange，提问作者NoobPro