如何通过函数实现多分段调研数据的批量聚合计算？

Great question! Having to write repetitive SELECT statements for every segment is a huge waste of time and invites errors. Here's how you can streamline this with grouping sets, reusable functions, or views to generate all your required aggregated results in one go:

1. Core Approach: Use GROUPING SETS for All Segments at Once #

Instead of writing separate queries for each segment (active, inactive, overall), use GROUPING SETS to generate all required segment combinations in a single query. This works with most modern SQL databases (PostgreSQL, SQL Server, MySQL 8+, etc.).

Full SQL Example #

This query calculates Top 3 Box scores for all 10 questions, covering:

• Overall survey results
• Results grouped by membership_status
• Results grouped by age_bracket
• Results grouped by the combination of membership_status and age_bracket

SELECT
  -- Optional: Grouping ID helps identify which segments are being calculated
  GROUPING_ID(d.membership_status, d.age_bracket) AS grouping_level,
  -- Replace NULLs with 'ALL' for readability in overall results
  COALESCE(d.membership_status, 'ALL') AS membership_status,
  COALESCE(d.age_bracket, 'ALL') AS age_bracket,
  
  -- Top 3 Box calculation for Question 1
  COUNT(DISTINCT CASE WHEN f.QUESTION_1 IN ('8','9','10') THEN f.RESPONDENT_ID END) * 1.0 /
  NULLIF(COUNT(DISTINCT CASE WHEN f.QUESTION_1 IS NOT NULL THEN f.RESPONDENT_ID END), 0) AS CSAT_1,
  
  -- Repeat the pattern for Questions 2-10
  COUNT(DISTINCT CASE WHEN f.QUESTION_2 IN ('8','9','10') THEN f.RESPONDENT_ID END) * 1.0 /
  NULLIF(COUNT(DISTINCT CASE WHEN f.QUESTION_2 IS NOT NULL THEN f.RESPONDENT_ID END), 0) AS CSAT_2,
  
  -- ... add CSAT_3 through CSAT_9 here ...
  
  COUNT(DISTINCT CASE WHEN f.QUESTION_10 IN ('8','9','10') THEN f.RESPONDENT_ID END) * 1.0 /
  NULLIF(COUNT(DISTINCT CASE WHEN f.QUESTION_10 IS NOT NULL THEN f.RESPONDENT_ID END), 0) AS CSAT_10
FROM FACT f
JOIN DIMENSION d ON f.RESPONDENT_ID = d.RESPONDENT_ID
-- Define all segment combinations we want to calculate
GROUP BY GROUPING SETS (
  (), -- Overall results (no grouping)
  (d.membership_status), -- Group by membership status
  (d.age_bracket), -- Group by age bracket
  (d.membership_status, d.age_bracket) -- Group by both dimensions
)
ORDER BY grouping_level, membership_status, age_bracket;

Key Notes: #

• NULLIF prevents division-by-zero errors when a segment has no valid responses for a question.
• COUNT(DISTINCT ...) ensures each respondent is only counted once per question/segment.
• GROUPING_ID returns a numeric value to distinguish grouping levels (0 = both dimensions, 1 = only age bracket, 2 = only membership status, 3 = overall).

2. Simplify Calculations with Custom Functions #

If you want to reduce repetition in the query, wrap the Top 3 Box logic in a custom function. This makes your main query cleaner and easier to maintain (if your Top 3 Box definition changes, you only update the function).

Example Function (PostgreSQL) #

-- Function to flag respondents who gave a Top 3 Box response
CREATE OR REPLACE FUNCTION is_top3box(question_response VARCHAR)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN question_response IN ('8','9','10');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Using the Function in Your Query #

SELECT
  COALESCE(d.membership_status, 'ALL') AS membership_status,
  COALESCE(d.age_bracket, 'ALL') AS age_bracket,
  
  COUNT(DISTINCT CASE WHEN is_top3box(f.QUESTION_1) THEN f.RESPONDENT_ID END) * 1.0 /
  NULLIF(COUNT(DISTINCT CASE WHEN f.QUESTION_1 IS NOT NULL THEN f.RESPONDENT_ID END), 0) AS CSAT_1,
  
  -- Repeat for other questions
  COUNT(DISTINCT CASE WHEN is_top3box(f.QUESTION_10) THEN f.RESPONDENT_ID END) * 1.0 /
  NULLIF(COUNT(DISTINCT CASE WHEN f.QUESTION_10 IS NOT NULL THEN f.RESPONDENT_ID END), 0) AS CSAT_10
FROM FACT f
JOIN DIMENSION d ON f.RESPONDENT_ID = d.RESPONDENT_ID
GROUP BY GROUPING SETS ((), (d.membership_status), (d.age_bracket), (d.membership_status, d.age_bracket))
ORDER BY membership_status, age_bracket;

3. Reuse Logic with a View #

If you run these calculations frequently, create a view to pre-process response flags. This separates the data transformation from the aggregation, making queries faster and easier to write.

Create the View #

CREATE VIEW survey_response_flags AS
SELECT
  f.RESPONDENT_ID,
  d.membership_status,
  d.age_bracket,
  -- Flag Top 3 Box and valid responses for each question
  CASE WHEN f.QUESTION_1 IN ('8','9','10') THEN 1 ELSE 0 END AS q1_top3box,
  CASE WHEN f.QUESTION_1 IS NOT NULL THEN 1 ELSE 0 END AS q1_valid,
  
  -- Repeat for Questions 2-10
  CASE WHEN f.QUESTION_10 IN ('8','9','10') THEN 1 ELSE 0 END AS q10_top3box,
  CASE WHEN f.QUESTION_10 IS NOT NULL THEN 1 ELSE 0 END AS q10_valid
FROM FACT f
JOIN DIMENSION d ON f.RESPONDENT_ID = d.RESPONDENT_ID;

Query the View #

SELECT
  COALESCE(membership_status, 'ALL') AS membership_status,
  COALESCE(age_bracket, 'ALL') AS age_bracket,
  
  COUNT(DISTINCT CASE WHEN q1_top3box = 1 THEN RESPONDENT_ID END) * 1.0 /
  NULLIF(COUNT(DISTINCT CASE WHEN q1_valid = 1 THEN RESPONDENT_ID END), 0) AS CSAT_1,
  
  -- Repeat for other questions
  COUNT(DISTINCT CASE WHEN q10_top3box = 1 THEN RESPONDENT_ID END) * 1.0 /
  NULLIF(COUNT(DISTINCT CASE WHEN q10_valid = 1 THEN RESPONDENT_ID END), 0) AS CSAT_10
FROM survey_response_flags
GROUP BY GROUPING SETS ((), (membership_status), (age_bracket), (membership_status, age_bracket))
ORDER BY membership_status, age_bracket;

Final Tips #

• Database Compatibility: GROUPING SETS is standard SQL, but if you're using an older database (e.g., MySQL <8.0), you can achieve the same result with UNION ALL of separate grouped queries (though GROUPING SETS is more efficient).
• Scalability: To add new segments (e.g., region), just add the field to the GROUPING SETS clause—no need to rewrite entire queries.
• Performance: Ensure RESPONDENT_ID is indexed in both tables to speed up the join and distinct counts.

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