sql - 用于对值 0-9、10.20 进行分组的多个 case 语句

Question

我在下面有第一个 case 语句，我需要将其与下面的 case 语句合并，以便像这样将值分组在一起:

days range   | Activity_submit
-------------------------------------
   0-9       |        11
  10-19      |        14
  20-29      |         3 

---

 SELECT 
    events_raw.properties ->> 'days_before_stay' AS "Days_before_stay",
    COUNT(CASE WHEN ((events_raw.properties ->> 'event_type') LIKE 
    'fs_mystay_activity_submit') THEN 1 ELSE NULL END) AS "Request Count"
    FROM events_raw

要与第一个合并的第二个 case 语句

select events_raw.properties ->> 'days_before_stay' AS "Days_before_stay" [days range], count(*) as [number of occurences]
from (
  select case  
    when days between 0 and 9 then ' 0- 9'
    when days between 10 and 19 then '10-19'
    else '20-99' end as range
  from events_raw) 
group by 1

Answer 1

我没有看到您的表结构，但这是您可以按表达式查看分组的示例。

SELECT 
    (id/10)*10 toOrder, -- addition field to sort by
    (id/10)*10 || ' - ' || ((id)/10+1)*10 AS range, 
    count(*) myCount
FROM report
GROUP BY 
    (id/10)*10,
    (id/10)*10 || ' - ' || ((id)/10+1)*10, -- group by expression
ORDER BY toOrder;

相反，如果 id 你可以使用任何整数字段。