查询以获取每个客户每天拥有的事务总数和每个客户每天每产品的事务数？
查询以获取每个客户每天拥有的事务总数和每个客户每天每产品的事务数？
提问于 2022-07-07 13:11:48
问题-编写一个查询，以获取每个客户每天拥有的事务总数和每个客户每天每个产品的事务数。订单从最近到最古老的事务dat？
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
Stack Overflow用户
发布于 2022-07-07 15:37:44
在MySQL 8+上，您可以在(分区)上使用COUNT(*)。分区按部分定义每天的总量和每天的总产品。
尝试：
with cte as (
select customer_name,
transaction_date,
product,
count(*) over (partition by customer_name,transaction_date ) as tot_per_day,
count(*) over (partition by customer_name,transaction_date,product ) as product_per_day
from customers c
inner join transactions_details td on c.customer_id=td.customer_id
) select cte.*
from cte
order by transaction_date desc; https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=04d6ef506dc0fe6ade23c288f0a6c797
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72898451
复制相关文章
相似问题
腾讯云开发者
