sql - 使用发票行项目汇总订单总数

Question

我试图通过查看 invoice_id 来计算每个客户的唯一预订数量，但是他们没有产生正确的结果。

架构

CREATE TABLE invoice_line_items (
    invoice_id int,
    customer_id int,
    tstamp datetime
);

数据

INSERT INTO invoice_line_items (invoice_id, customer_id, tstamp)
VALUES ('1', '123', '2018-12-21 10:00:00'),
('1', '123', '2018-12-21 10:00:00'),
('2', '123', '2018-12-22 10:00:00'),
('2', '124', '2018-12-22 10:00:00'),
('3', '124', '2018-12-22 10:00:00'),
('4', '124', '2018-12-22 10:00:00'),
('5', '124', '2018-12-22 10:00:00'),
('5', '124', '2018-12-22 10:00:00');

查询

select customer_id, count(*) as number_of_orders
from invoice_line_items
where tstamp >= '2018-01-01'
group by customer_id, invoice_id

期望的输出

customer_id | number_of_orders 
123         | 2          
124         | 4

Answer 1

您不需要在 group by 中包含 invoice_id :

select customer_id, count(distinct invoice_id) as number_of_orders
from invoice_line_items
where tstamp >= '2018-01-01'
group by customer_id;

在 COUNT() 中使用 DISTINCT 查找唯一预订。