查询用户职位发布次数分布的最优SQL实现方法咨询

Great question! Let's start by unpacking what your current query is doing, then look at more efficient approaches based on your actual business need (since there's a slight ambiguity in the requirement description).

First: Clarify the Requirement #

Your goal is to count two groups of users:

1. Users who posted a job only once
2. Users who posted jobs multiple times

But we need to clarify what "posted once" means here—there are two common interpretations, and each has a streamlined solution:

Scenario 1: Count users by number of distinct jobs posted #

If "posted once" means the user has only ever posted one unique job (regardless of how many times they reposted that same job on different dates), we can simplify the query to a single level of aggregation:

SELECT
  SUM(CASE WHEN distinct_job_count = 1 THEN 1 ELSE 0 END) AS posted_once,
  SUM(CASE WHEN distinct_job_count > 1 THEN 1 ELSE 0 END) AS posted_multiple_times
FROM (
  SELECT 
    user_id,
    COUNT(DISTINCT job_id) AS distinct_job_count
  FROM your_table
  GROUP BY user_id
) user_job_counts;

Why this is better:

• Avoids the extra CTE and second aggregation step in your original query.
• Directly calculates the metric you need in one pass over the data (grouping by user once, not twice).

Scenario 2: Count users by number of unique posting dates #

If "posted once" means the user has only ever posted a job on one unique date (regardless of how many different jobs they posted that day), the query is just as simple:

SELECT
  SUM(CASE WHEN post_date_count = 1 THEN 1 ELSE 0 END) AS posted_once,
  SUM(CASE WHEN post_date_count > 1 THEN 1 ELSE 0 END) AS posted_multiple_times
FROM (
  SELECT 
    user_id,
    COUNT(DISTINCT date_posted) AS post_date_count
  FROM your_table
  GROUP BY user_id
) user_post_dates;

Scenario 3: Match your original query's logic #

If your original query was intended to count users where their average number of posting dates per job is 1 vs greater than 1 (e.g., a user who posts 2 different jobs once each has an average of 1; a user who posts 1 job 3 times has an average of 3), we can still eliminate the redundant CTE:

SELECT
  SUM(CASE WHEN avg_posts_per_job > 1 THEN 1 ELSE 0 END) AS posted_multiple_times,
  SUM(CASE WHEN avg_posts_per_job = 1 THEN 1 ELSE 0 END) AS posted_once
FROM (
  SELECT 
    user_id,
    -- Cast to float to avoid integer division issues
    COUNT(DISTINCT date_posted)::FLOAT / COUNT(DISTINCT job_id) AS avg_posts_per_job
  FROM your_table
  GROUP BY user_id
) user_avg_posts;

Why this beats your original query:

Your original query first groups by user_id + job_id to calculate per-job post counts, then groups again by user_id to average those counts. This creates an intermediate result set with one row per user-job pair, which is unnecessary. The optimized version calculates the average directly in a single grouping step, reducing the amount of data processed and improving query speed.

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