BigQuery横向/纵向数据优劣对比及企业利润累积求和咨询

Hey there! Let's tackle your two BigQuery questions one by one, based on the table structure you shared.

The answer depends on your core use case, but let's break down the pros and cons clearly:

• 横向表（table1）

  • Pros: Super intuitive at a glance—you can see all monthly profits for a single company in one row, which is great for quick, ad-hoc checks of small datasets.
  • Cons: Terrible scalability. Adding a new month (like May) requires modifying the table schema to add a new column, which is a hassle in production. It also violates database normalization rules, leading to potential redundancy, and makes most analytical operations (like aggregations or window functions) require extra transposition steps.

• 纵向表（table2）

  • Pros: Extremely flexible—adding a new month just means inserting new rows, no schema changes needed. It’s optimized for analytics: window functions, group aggregations, and trend analysis become far simpler. It also follows database normalization principles, keeping data redundancy low.
  • Cons: Less visually intuitive for single-company overviews (each row only shows one month per company), but this is easily fixed with query result visualization or pivot operations when needed.

Bottom line: If your main goal is data analysis, reporting, or long-term data maintenance, the纵向表 (long table) is hands-down the better choice.横向 tables are only useful for very small, static datasets where you just need quick visual checks.

We’ll cover solutions for both table structures, since you might need to work with either:

Option 1: Calculate from 横向表 (table1) #

First, we need to unpivot the横向 table into a long format, then apply a window function to compute the cumulative sum:

#standardSQL
WITH table1 AS (
  SELECT "A" AS company, 345 AS Jan, 456 AS Feb, 543 AS March, 987 AS April UNION ALL
  SELECT "B", 405, 956, 673, 907 UNION ALL
  SELECT "C", 745, 476, 323, 477
),
-- Convert wide table to long format using UNPIVOT
unpivoted_table AS (
  SELECT 
    company,
    month,
    profit
  FROM table1
  UNPIVOT(
    profit FOR month IN (Jan, Feb, March, April)
  )
)
SELECT
  company,
  month,
  profit,
  -- Compute cumulative sum per company, ordered by month
  SUM(profit) OVER (
    PARTITION BY company 
    ORDER BY 
      CASE month 
        WHEN 'Jan' THEN 1 
        WHEN 'Feb' THEN 2 
        WHEN 'March' THEN 3 
        WHEN 'April' THEN 4 
      END
  ) AS cumulative_profit
FROM unpivoted_table
ORDER BY company, cumulative_profit;

Option 2: Calculate from 纵向表 (table2) #

Assuming table2 has the schema company STRING, month STRING, profit INT64, the code is much cleaner because we don’t need to unpivot first:

#standardSQL
SELECT
  company,
  month,
  profit,
  SUM(profit) OVER (
    PARTITION BY company 
    ORDER BY 
      -- Use CASE to ensure correct month order (adjust if your month format is numeric)
      CASE month 
        WHEN 'Jan' THEN 1 
        WHEN 'Feb' THEN 2 
        WHEN 'March' THEN 3 
        WHEN 'April' THEN 4 
      END
  ) AS cumulative_profit
FROM table2
ORDER BY company, cumulative_profit;

How this works: #

The SUM() OVER (PARTITION BY company ORDER BY ...) window function does two key things:

• PARTITION BY company: Ensures we calculate cumulative sums separately for each company.
• ORDER BY: Guarantees we sum profits in the correct January-to-April order, so each row shows the total profit from the start of the year up to that month.

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