将独立查询结果合并为单表：MySQL多时段营收统计问询

Got it, let's break this down. To merge your four revenue calculations into a single result set, we'll use UNION ALL (more efficient than UNION since we don't need deduplication here) to stack the outputs of each query—each query will return the same three columns: revenue_type (to identify which stat it is), period_identifier (the specific time frame, or "all time" for the full period), and total_revenue (the calculated amount).

First, let's assume your tables have these fields (adjust if yours differ):

• transaction: transaction_id, itemid, transaction_time (datetime), quantity
• price: itemid, unit_price (the price at transaction time; if you have historical pricing, see the notes at the end)

Full SQL Query #

-- 1. Full-period total revenue
SELECT 
    '全周期总营收' AS revenue_type,
    '所有时间段' AS period_identifier,
    SUM(t.quantity * p.unit_price) AS total_revenue
FROM transaction t
JOIN price p ON t.itemid = p.itemid

UNION ALL

-- 2. Daily revenue
SELECT 
    '单日营收' AS revenue_type,
    DATE(t.transaction_time) AS period_identifier,
    SUM(t.quantity * p.unit_price) AS total_revenue
FROM transaction t
JOIN price p ON t.itemid = p.itemid
GROUP BY DATE(t.transaction_time)

UNION ALL

-- 3. Weekly revenue (uses year-week format, e.g., 2024-32 for week 32 of 2024)
SELECT 
    '单周营收' AS revenue_type,
    DATE_FORMAT(t.transaction_time, '%Y-%u') AS period_identifier,
    SUM(t.quantity * p.unit_price) AS total_revenue
FROM transaction t
JOIN price p ON t.itemid = p.itemid
GROUP BY DATE_FORMAT(t.transaction_time, '%Y-%u')

UNION ALL

-- 4. Monthly revenue (uses year-month format, e.g., 2024-08 for August 2024)
SELECT 
    '单月营收' AS revenue_type,
    DATE_FORMAT(t.transaction_time, '%Y-%m') AS period_identifier,
    SUM(t.quantity * p.unit_price) AS total_revenue
FROM transaction t
JOIN price p ON t.itemid = p.itemid
GROUP BY DATE_FORMAT(t.transaction_time, '%Y-%m');

Key Explanations #

• UNION ALL: This combines the results of each query into one table, preserving all rows (no deduplication, which is faster here since each query's revenue_type is unique).
• Column Consistency: Every SELECT returns the same three columns, which is required for UNION operations.
• Grouping Logic:
  • Daily stats group by the date part of transaction_time.
  • Weekly stats use %Y-%u to get a unique year-week identifier (adjust the format if you prefer showing the start/end date of the week instead).
  • Monthly stats use %Y-%m to group by year and month.

Important Notes #

• Historical Pricing: If your price table stores historical prices (e.g., with effective_start and effective_end dates), you'll need to adjust the JOIN to match the transaction time to the correct price period:

  JOIN price p ON t.itemid = p.itemid 
  AND t.transaction_time BETWEEN p.effective_start AND p.effective_end
  

• Filtering Valid Transactions: If your transaction table has canceled/refunded entries, add a WHERE clause (e.g., WHERE t.status = 'completed') to each query to exclude them.
• Custom Date Formats: Modify the DATE_FORMAT parameters to match your preferred display (e.g., '%Y年%m月' for Chinese-style month labels).

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