基于user_Id统计两表仅单表/共存在用户占比的SQL查询需求

Got it, let's work through this problem. You want to calculate the percentage of three user groups—only PC, only phone, and both—without relying on UNION (to avoid repeated table scans), and ensure the percentages add up to 100%. Here's a clean, efficient approach:

Core Approach #

We'll use a FULL OUTER JOIN to combine all users from both tables, then categorize each user with a CASE statement. After counting users per category, we'll compute percentages relative to the total unique user count.

Complete SQL Query #

WITH user_categories AS (
    SELECT
        CASE
            WHEN PC.user_Id IS NOT NULL AND phone.user_Id IS NULL THEN 'PC_only'
            WHEN PC.user_Id IS NULL AND phone.user_Id IS NOT NULL THEN 'phone_only'
            ELSE 'both'
        END AS user_category,
        -- Count each user once, even if they have multiple Page entries in a table
        COUNT(DISTINCT COALESCE(PC.user_Id, phone.user_Id)) AS user_count
    FROM PC
    FULL OUTER JOIN phone ON PC.user_Id = phone.user_Id
    GROUP BY user_category
)
SELECT
    user_category,
    ROUND((user_count::FLOAT / SUM(user_count) OVER ()) * 100, 2) AS percentage
FROM user_categories
ORDER BY user_category;

Breakdown of the Query #

1. CTE user_categories:

   • FULL OUTER JOIN pulls in every user present in either the PC or phone table (or both).
   • The CASE statement labels each user into one of the three groups based on their presence in the tables.
   • COUNT(DISTINCT COALESCE(...)) ensures we count each unique user only once—critical if your tables have multiple rows per user (e.g., different Page entries for the same user).

2. Final Calculation:

   • SUM(user_count) OVER () uses a window function to get the total number of unique users across all categories, eliminating the need for a separate CTE or subquery.
   • ::FLOAT (or CAST(user_count AS FLOAT) in some SQL dialects) ensures we do decimal division instead of integer division, so percentages are accurate.
   • ROUND(..., 2) formats the percentage to two decimal places for readability.

Alternative for Dialects Without FULL OUTER JOIN #

If your SQL dialect (like MySQL) doesn't support FULL OUTER JOIN, you can simulate it with a LEFT JOIN and RIGHT JOIN combined, but this does use a single UNION to avoid duplicate users:

WITH all_users AS (
    SELECT user_Id FROM PC
    UNION
    SELECT user_Id FROM phone
),
user_categories AS (
    SELECT
        CASE
            WHEN EXISTS (SELECT 1 FROM PC WHERE PC.user_Id = all_users.user_Id)
             AND NOT EXISTS (SELECT 1 FROM phone WHERE phone.user_Id = all_users.user_Id) THEN 'PC_only'
            WHEN EXISTS (SELECT 1 FROM phone WHERE phone.user_Id = all_users.user_Id)
             AND NOT EXISTS (SELECT 1 FROM PC WHERE PC.user_Id = all_users.user_Id) THEN 'phone_only'
            ELSE 'both'
        END AS user_category,
        COUNT(user_Id) AS user_count
    FROM all_users
    GROUP BY user_category
)
SELECT
    user_category,
    ROUND((user_count::FLOAT / SUM(user_count) OVER ()) * 100, 2) AS percentage
FROM user_categories
ORDER BY user_category;

This version first gets all unique users, then categorizes them—still avoiding repeated full table scans beyond the initial UNION.

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