在Google BigQuery中按用户与月份分组的SQL查询求助

Hey there! Let's fix this up for you—since you're new to BigQuery, I'll break down both of your desired output formats step by step, and also explain why your initial attempts might have hit snags.

First: Why Your Initial Tries Didn't Work #

BigQuery doesn't have a standalone month() function for timestamps directly—plus, grouping by just the month number would mix January 2017 with January 2018, which isn't what you want. Instead, you need to group by the full year-month combination using TIMESTAMP_TRUNC or formatted timestamp strings to keep each month-year unique.

Form 2: One Row Per Customer-Month (Easier for Analysis) #

This format is more flexible for most analytical tasks (like filtering by specific months or trending over time). We'll group by both email and the truncated month timestamp (or a readable year-month string), then calculate your desired metrics.

SELECT
  email,
  MIN(address.zip) AS zip,
  MIN(country) AS country,
  MIN(source) AS source,
  -- Create a readable month label (e.g., "2017_January")
  FORMAT_TIMESTAMP("%Y_%B", created_at) AS month,
  -- Or use a timestamp for the start of the month if you need date sorting:
  -- TIMESTAMP_TRUNC(created_at, MONTH) AS month_start,
  COUNT(order_number) AS number_orders,
  SUM(total_price) AS total_spent
FROM orders_data
GROUP BY email, month
-- Uncomment below if using month_start instead of formatted string:
-- GROUP BY email, month_start
ORDER BY email, month

Key Notes: #

• FORMAT_TIMESTAMP("%Y_%B", created_at) generates exactly the month label you mentioned (e.g., 2017_January).
• We keep MIN(address.zip), MIN(country), etc., since these are customer-level attributes (assuming each customer has consistent values—if not, you might want to validate that data first).
• This query will return one row for every customer-month combination where the customer placed an order.

Form 1: One Row Per Customer With Monthly Columns (Pivoted for Reporting) #

If you want all monthly metrics as columns in a single row per customer, we'll use BigQuery's PIVOT function. Since you need two metrics per month (spent and order count), we'll pivot both in one query.

Static Pivot (Manual Month List) #

Great if you know your exact date range (2017-2020) and want explicit column names:

WITH monthly_customer_agg AS (
  SELECT
    email,
    MIN(address.zip) AS zip,
    MIN(country) AS country,
    MIN(source) AS source,
    FORMAT_TIMESTAMP("%Y_%B", created_at) AS month,
    COUNT(order_number) AS number_orders,
    SUM(total_price) AS total_spent
  FROM orders_data
  GROUP BY email, month
)
SELECT *
FROM monthly_customer_agg
PIVOT (
  SUM(number_orders) AS num_orders,
  SUM(total_spent) AS spent
  FOR month IN (
    '2017_January', '2017_February', '2017_March',
    -- Add all months from 2017 to 2020 here...
    '2020_November', '2020_December'
  )
)
ORDER BY email

Dynamic Pivot (Auto-Generate Month Columns) #

If you don't want to list every month manually, use EXECUTE IMMEDIATE to dynamically generate the pivot columns based on your data:

DECLARE months ARRAY<STRING>;

-- Get all unique year-month labels from your data
SET months = ARRAY(
  SELECT DISTINCT FORMAT_TIMESTAMP("%Y_%B", created_at)
  FROM orders_data
  WHERE created_at BETWEEN TIMESTAMP('2017-01-01') AND TIMESTAMP('2020-12-31')
  ORDER BY 1
);

-- Build and run the pivot query dynamically
EXECUTE IMMEDIATE '''
WITH monthly_customer_agg AS (
  SELECT
    email,
    MIN(address.zip) AS zip,
    MIN(country) AS country,
    MIN(source) AS source,
    FORMAT_TIMESTAMP("%Y_%B", created_at) AS month,
    COUNT(order_number) AS number_orders,
    SUM(total_price) AS total_spent
  FROM orders_data
  GROUP BY email, month
)
SELECT *
FROM monthly_customer_agg
PIVOT (
  SUM(number_orders) AS num_orders,
  SUM(total_spent) AS spent
  FOR month IN (''' || STRING_AGG(FORMAT("'%s'", month), ', ') || ''')
)
ORDER BY email
''';

Key Notes: #

• The static pivot is simpler for beginners, while the dynamic version scales if your date range changes.
• Columns will be named like 2017_January_num_orders and 2017_January_spent, matching your request.
• Customers with no orders in a month will show NULL for that month's columns—you can use IFNULL() to replace NULL with 0 if needed (e.g., IFNULL(2017_January_spent, 0) AS 2017_January_spent).

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