如何按日期与公司统计属性(bit_id)的空值数量

Got it, let's tackle this problem—you need to capture and count the total number of null bit_id values grouped by date and company, right? I'll walk you through the standard approach, plus some edge case solutions depending on your database.

The core goal here is to group your data by your date and company dimensions, then tally up how many rows have a null bit_id in each unique group.

Standard SQL Query (Works for Most Databases) #

This is the simplest approach if you only care about date-company pairs that actually have null bit_id values:

SELECT
  date_column,
  company_column,
  COUNT(*) AS null_bit_id_count
FROM
  your_table_name
WHERE
  bit_id IS NULL
GROUP BY
  date_column,
  company_column
ORDER BY
  date_column DESC,
  company_column;

Let's break this down: #

• WHERE bit_id IS NULL: Filters your dataset to only include rows where bit_id is null—so we're only counting the records we care about.
• GROUP BY date_column, company_column: Clusters the filtered rows into unique date-company pairs, so we get a count per combination.
• COUNT(*): Counts every row in each group (since we already filtered for nulls, this is exactly the number of null bit_id values per group).
• ORDER BY: Optional, but sorting by date (newest first) and company makes the results easier to scan.

Include Groups with 0 Nulls (Optional) #

If you want to see every possible date-company pair—even those with no null bit_id values (showing 0 instead of omitting them)—you'll need to generate a full list of date-company combinations first, then join back to your data. Here's an example for PostgreSQL/BigQuery:

-- Generate all possible date-company pairs first
WITH all_date_company AS (
  SELECT
    d.date_column,
    c.company_column
  FROM
    (SELECT DISTINCT date_column FROM your_table_name) d
  CROSS JOIN
    (SELECT DISTINCT company_column FROM your_table_name) c
)
SELECT
  adc.date_column,
  adc.company_column,
  COALESCE(COUNT(t.bit_id IS NULL OR NULL), 0) AS null_bit_id_count
FROM
  all_date_company adc
LEFT JOIN
  your_table_name t
ON
  adc.date_column = t.date_column
  AND adc.company_column = t.company_column
  AND t.bit_id IS NULL
GROUP BY
  adc.date_column,
  adc.company_column
ORDER BY
  adc.date_column DESC,
  adc.company_column;

Database-Specific Tweaks: #

• MySQL: Replace COUNT(t.bit_id IS NULL OR NULL) with SUM(CASE WHEN t.bit_id IS NULL THEN 1 ELSE 0 END) to get the same result.
• BigQuery: If your date column is a timestamp, use DATE(timestamp_column) to truncate it to a calendar day before grouping.
• SQL Server: Use ISNULL instead of COALESCE if you prefer—both work for replacing null counts with 0.

Quick Setup Reminder #

Don't forget to swap out these placeholders with your actual column/table names:

• date_column: Your date dimension column (e.g., transaction_date)
• company_column: Your company identifier column (e.g., company_id or company_name)
• your_table_name: The name of the table holding your data

That should give you exactly the grouped null count you need!

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