计算秒级时长占比及筛选Enquiry阶段占比≥50%用户的SQL求助

Hey there! Let's work through your two requirements clearly, starting with the user segmentation problem you're having trouble with.

The approach here depends on what you're targeting with the 50% calculation:

• For 50% of each individual record's duration:

  SELECT customer_id, durationInSeconds, durationInSeconds * 0.5 AS half_duration
  FROM your_table_name;
  

• For 50% of the total duration across all records:

  SELECT SUM(durationInSeconds) * 0.5 AS total_half_duration
  FROM your_table_name;
  

• For 50% of each user's total combined duration:

  SELECT customer_id, SUM(durationInSeconds) * 0.5 AS user_total_half_duration
  FROM your_table_name
  GROUP BY customer_id;
  

Your core logic is correct—most likely the issue is either forgetting to group by customer_id (so you're aggregating across all users instead of per user) or hitting integer division (which would make ratios like 5/10 return 0 instead of 0.5 in some databases). Here's the corrected, robust query:

SELECT 
  customer_id,
  SUM(CASE WHEN Sub_Journey_Stage = 'Enquiry' THEN durationInSeconds ELSE 0 END) AS enquiry_total_seconds,
  SUM(durationInSeconds) AS user_total_seconds,
  -- Calculate the percentage with decimal precision
  ROUND(
    CAST(SUM(CASE WHEN Sub_Journey_Stage = 'Enquiry' THEN durationInSeconds ELSE 0 END) AS DECIMAL) 
    / NULLIF(SUM(durationInSeconds), 0) * 100,
    2
  ) AS enquiry_duration_percentage
FROM your_table_name
GROUP BY customer_id
-- Filter for users meeting the 50% threshold
HAVING CAST(SUM(CASE WHEN Sub_Journey_Stage = 'Enquiry' THEN durationInSeconds ELSE 0 END) AS DECIMAL) 
       / NULLIF(SUM(durationInSeconds), 0) >= 0.5;

Key fixes explained: #

• GROUP BY customer_id: This ensures all calculations are aggregated per individual user, which is critical for your per-user segmentation requirement.
• CAST(...) AS DECIMAL: Avoids integer division pitfalls—many databases treat integer/integer divisions as whole numbers, so casting to a decimal type gives you the precise ratio you need.
• NULLIF(SUM(durationInSeconds), 0): Prevents division-by-zero errors for users with a total duration of 0 (these users will be excluded automatically since the ratio becomes NULL, which doesn't meet the >=0.5 condition).
• ROUND(..., 2): Optional but helpful for readability, rounding the percentage to two decimal places.

内容的提问来源于stack exchange，提问作者G Raghavender Yadav