SQL技术问询：如何将单行年度财务收入拆分为四条季度记录（季度收入均分场景）

Hey there! You’re spot-on that CROSS JOIN is the right tool for this job—it’s designed to pair every row from one dataset with every row from another, which is exactly what we need to turn a single annual record into four quarterly ones. Let’s break this down with concrete examples.

Core Concept #

We’ll create a small dataset that represents the 4 quarters, then use CROSS JOIN to combine each annual record with each quarter entry. Then we just divide the total annual revenue by 4 to get the equal quarterly amount.

Step 1: Define Your Source Table #

First, let’s assume your source table looks something like this (adjust column names to match your actual schema):

CREATE TABLE financial_records (
  record_id INT PRIMARY KEY,
  fiscal_year INT,
  total_annual_revenue DECIMAL(10,2),
  business_unit VARCHAR(50)
);

Sample source data:

Step 2: Use CROSS JOIN to Split Rows #

Here’s the SQL query that does the magic. We’ll use a subquery to generate the 4 quarters, then cross join it with your financial records:

For MySQL, PostgreSQL, or SQL Server:

SELECT
  fr.record_id,
  fr.fiscal_year,
  q.quarter_number,
  -- Calculate equal quarterly revenue, rounded to 2 decimal places
  ROUND(fr.total_annual_revenue / 4, 2) AS quarterly_revenue,
  fr.business_unit
FROM
  financial_records fr
CROSS JOIN (
  -- This subquery creates a list of 4 quarters
  SELECT 1 AS quarter_number UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4
) q;

What This Does: #

• The subquery (SELECT 1 UNION ALL ...) creates a temporary table with 4 rows, one for each quarter.
• CROSS JOIN takes every row from financial_records and pairs it with every row from the quarter list—so each annual record becomes 4 rows.
• We divide the total annual revenue by 4 to get the equal quarterly amount, using ROUND() to keep currency formatting clean.

Sample Output #

Running the query on the sample data above will give you:

Extra Tips #

• If your revenue doesn’t divide evenly by 4 and you need to adjust for pennies (e.g., make the first 3 quarters $X and the 4th $X+1), you can use a CASE statement to tweak the calculation.
• If you frequently need this quarter list, you can create a permanent lookup table (e.g., quarters with quarter_number and quarter_name columns) instead of using a subquery.

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