BigQuery SQL：计算关联表中每个主体前N条关联记录的数值总和

Alright, let's tackle this problem. Your current query sums up all invoices for each company, but we need to narrow it down to just the first 6 invoices (sorted by invoice ID ascending) and calculate their total. Here's how to make that happen in BigQuery:

Step-by-Step Solution #

First, we need to assign a rank to each invoice within its company group, then filter to keep only the first 6 ranks, and finally sum those amounts. Here's the working query:

WITH ranked_invoices AS (
  SELECT
    company_id,
    amount,
    -- Assign a unique rank to each invoice per company, ordered by ID
    ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY id ASC) AS invoice_rank
  FROM invoices
)
SELECT
  c.id AS company_id,
  c.name AS company_name, -- Include any company fields you need
  SUM(r.amount) AS top_6_invoices_total
FROM ranked_invoices r
JOIN companies c ON r.company_id = c.id
WHERE r.invoice_rank <= 6
GROUP BY c.id, c.name;

Let's break down what's happening here: #

• CTE (ranked_invoices): This temporary table adds an invoice_rank column. The ROW_NUMBER() window function splits the data by company_id (so each company gets its own set of ranks) and orders by id ASC to ensure we're grabbing the earliest invoices first.
• Filter & Aggregate: We then filter out any invoices ranked higher than 6, join with the companies table if you need company details, and sum the amount for each company to get the total of their first 6 invoices.
• Handling Smaller Companies: If a company has fewer than 6 invoices, this query will automatically sum all their available invoices—no extra logic needed for that edge case.

More Concise Alternative with QUALIFY #

BigQuery has a handy QUALIFY clause that lets you filter rows directly using window functions, eliminating the need for a CTE. Here's a shorter version:

SELECT
  c.id AS company_id,
  c.name AS company_name,
  SUM(i.amount) AS top_6_invoices_total
FROM invoices i
JOIN companies c ON i.company_id = c.id
QUALIFY ROW_NUMBER() OVER (PARTITION BY i.company_id ORDER BY i.id ASC) <= 6
GROUP BY c.id, c.name;

Note on Ties #

If you ever have invoices with duplicate IDs (unlikely, but possible), you could swap ROW_NUMBER() with RANK() or DENSE_RANK() to handle ties differently. But since invoice IDs are typically unique, ROW_NUMBER() is the right choice here.

内容的提问来源于stack exchange，提问作者Tom Hamilton Stubber