如何编写SQL实现按客户、订单日期对发票按商品数量1-10分桶（Bucket）统计

Solution #

Got it, let's build on your initial query to get the exact result you need. The key here is to first calculate how many items are in each invoice, then use conditional aggregation to count how many invoices fall into each 1-10 bucket, plus sum up the total items per customer per day.

First, let's break down the steps:

• Calculate items per invoice (your existing query does this—we'll wrap it for reuse)
• Aggregate by customer and date, using conditional logic to tally invoices in each bucket

Here's the full SQL query that produces your expected output:

WITH invoice_item_counts AS (
    SELECT 
        order_date,
        customer_id,
        invc_num,
        COUNT(item_sku) AS item_count
    FROM customer_invoice_table
    GROUP BY order_date, customer_id, invc_num
)
SELECT
    order_date AS "Order Date MMDDYYYY",
    customer_id AS "Customer Id",
    SUM(CASE WHEN item_count = 1 THEN 1 ELSE 0 END) AS "Bucket 1",
    SUM(CASE WHEN item_count = 2 THEN 1 ELSE 0 END) AS "Bucket 2",
    SUM(CASE WHEN item_count = 3 THEN 1 ELSE 0 END) AS "Bucket 3",
    SUM(CASE WHEN item_count = 4 THEN 1 ELSE 0 END) AS "Bucket 4",
    SUM(CASE WHEN item_count = 5 THEN 1 ELSE 0 END) AS "Bucket 5",
    SUM(CASE WHEN item_count = 6 THEN 1 ELSE 0 END) AS "Bucket 6",
    SUM(CASE WHEN item_count = 7 THEN 1 ELSE 0 END) AS "Bucket 7",
    SUM(CASE WHEN item_count = 8 THEN 1 ELSE 0 END) AS "Bucket 8",
    SUM(CASE WHEN item_count = 9 THEN 1 ELSE 0 END) AS "Bucket 9",
    SUM(CASE WHEN item_count = 10 THEN 1 ELSE 0 END) AS "Bucket 10",
    SUM(item_count) AS "Total Items Ordered"
FROM invoice_item_counts
GROUP BY order_date, customer_id
ORDER BY order_date, customer_id;

How it works: #

• CTE invoice_item_counts: This first step computes the number of items in each individual invoice. For example, invoice 05 for customer 3 on 04/02/2021 has 3 items, so this row will have item_count = 3.
• Conditional aggregation for buckets: Each SUM(CASE...) checks if an invoice's item count matches the bucket number. If yes, it adds 1 to the bucket count; otherwise, it adds 0. This gives us the number of invoices in each bucket per customer per day.
• Total Items Ordered: Summing up all item_count values for a customer on a date gives the total number of items they ordered that day.

Alternative for databases without CTE support (like older MySQL versions): #

If your database doesn't support CTEs, you can use a subquery instead:

SELECT
    order_date AS "Order Date MMDDYYYY",
    customer_id AS "Customer Id",
    SUM(CASE WHEN item_count = 1 THEN 1 ELSE 0 END) AS "Bucket 1",
    SUM(CASE WHEN item_count = 2 THEN 1 ELSE 0 END) AS "Bucket 2",
    SUM(CASE WHEN item_count = 3 THEN 1 ELSE 0 END) AS "Bucket 3",
    SUM(CASE WHEN item_count = 4 THEN 1 ELSE 0 END) AS "Bucket 4",
    SUM(CASE WHEN item_count = 5 THEN 1 ELSE 0 END) AS "Bucket 5",
    SUM(CASE WHEN item_count = 6 THEN 1 ELSE 0 END) AS "Bucket 6",
    SUM(CASE WHEN item_count = 7 THEN 1 ELSE 0 END) AS "Bucket 7",
    SUM(CASE WHEN item_count = 8 THEN 1 ELSE 0 END) AS "Bucket 8",
    SUM(CASE WHEN item_count = 9 THEN 1 ELSE 0 END) AS "Bucket 9",
    SUM(CASE WHEN item_count = 10 THEN 1 ELSE 0 END) AS "Bucket 10",
    SUM(item_count) AS "Total Items Ordered"
FROM (
    SELECT 
        order_date,
        customer_id,
        invc_num,
        COUNT(item_sku) AS item_count
    FROM customer_invoice_table
    GROUP BY order_date, customer_id, invc_num
) AS invoice_item_counts
GROUP BY order_date, customer_id
ORDER BY order_date, customer_id;

This will produce exactly the output you showed in your example—for instance, customer 4 on 04/03/2021 has 1 invoice with 1 item (Bucket 1 = 1) and 1 invoice with 10 items (Bucket 10 = 1), total items 1 + 10 = 11, which matches your expected result.

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