在选定的案例中有好的解决方案吗?我的select查询看起来非常愚蠢.:))

Question

select 
code,
max(age) age_level,
max(age_interval) age_interval,
sum(total) total
from 
(select 
(case  
when floor(months_between(sysdate, u.birth_date)/12) between 13 and 18 then 'Өсвөр үе' 
when floor(months_between(sysdate, u.birth_date)/12) between 19 and 25 then 'Залуу үе'
when floor(months_between(sysdate, u.birth_date)/12) between 26 and 35 then 'Идэр үе'
when floor(months_between(sysdate, u.birth_date)/12) between 36 and 45 then 'Хижээл үе'
when floor(months_between(sysdate, u.birth_date)/12) between 46 and 60 then 'Өтөл үе'            
when floor(months_between(sysdate, u.birth_date)/12) between 60 and 100 then 'Өндөр үе'
else 'other'
end) age,
(case  
when floor(months_between(sysdate, u.birth_date)/12) between 13 and 18 then 'kid'
when floor(months_between(sysdate, u.birth_date)/12) between 19 and 25 then 'bigger_kid'
when floor(months_between(sysdate, u.birth_date)/12) between 26 and 35 then 'adult'
when floor(months_between(sysdate, u.birth_date)/12) between 36 and 45 then 'bigger_adult'
when floor(months_between(sysdate, u.birth_date)/12) between 46 and 60 then 'big_adult'            
when floor(months_between(sysdate, u.birth_date)/12) between 60 and 100 then 'caption'
else 'other'
end) code,
(case  
when floor(months_between(sysdate, u.birth_date)/12) between 13 and 18 then '13-18'
when floor(months_between(sysdate, u.birth_date)/12) between 19 and 25 then '19-25'
when floor(months_between(sysdate, u.birth_date)/12) between 26 and 35 then '26-35'
when floor(months_between(sysdate, u.birth_date)/12) between 36 and 45 then '36-45'
when floor(months_between(sysdate, u.birth_date)/12) between 46 and 60 then '46-60'            
when floor(months_between(sysdate, u.birth_date)/12) between 60 and 100 then '60-100'
else 'other'
end) age_interval,
count(u.id) total
from sec_survey_users su 
left join sec_users u on su.user_id = u.id 
where su.survey_id = 'D3A21B1C2D8334C9E055824F7FFC5DF4' group by u.birth_date) mtable group by mtable.code;

Answer 1

这样的操作可以减少重复(或者在永久表中定义年龄范围并跳过with子句(：

with age_classes (min_age, max_age, age_interval, code, age) as
( select 13, 18,  '13-18',  'teen',                   'Teenager'       from dual union all
select 19, 25,  '19-25',  'young',                  'Young adult'    from dual union all
select 26, 35,  '26-35',  'still_young',            'It won''t last' from dual union all
select 36, 45,  '36-45',  'slightly_less_young',    'Middle aged'    from dual union all
select 46, 50,  '46-60',  'still_got_it',           'Still got it'   from dual union all    
select 60, 100, '60-100', 'exceeding_expectations', 'Free bus pass'  from dual
)
select nvl(ac.code,'other') as code
, nvl(ac.age,'other')  as age
, nvl(ac.age_interval,'other') as age_interval
, count(*) total
from   sec_survey_users su 
left join sec_users u on su.user_id = u.id 
left join age_classes ac on floor(months_between(sysdate, u.birth_date)/12) between ac.min_age and ac.max_age
group by
ac.code
, ac.age
, ac.age_interval;

使用永久表，可以将age_interval标签定义为连接最小/最大年龄列的虚拟列，以避免一些冗余。该表还将方便报告应用程序等中的下拉列表。

Answer 2

您可以使用CTE重写查询，以防止重复写入FLOOR(MONTHS_BETWEEN(sysdate, u.birth_date) / 12)，其和年龄别名形式将在GROUP BY列表(如(中使用

WITH u0 AS
(SELECT FLOOR(MONTHS_BETWEEN(sysdate, birth_date) / 12) AS age, COUNT(id) AS total
FROM sec_users
GROUP BY FLOOR(MONTHS_BETWEEN(sysdate, birth_date) / 12)),
u2 AS
(SELECT (CASE
WHEN age BETWEEN 13 AND 18 THEN
'Өсвөр үе'
WHEN age BETWEEN 19 AND 25 THEN
'Залуу үе'
WHEN age BETWEEN 26 AND 35 THEN
'Идэр үе'
WHEN age BETWEEN 36 AND 45 THEN
'Хижээл үе'
WHEN age BETWEEN 46 AND 60 THEN
'Өтөл үе'
WHEN age BETWEEN 61 AND 100 THEN
'Өндөр үе'
ELSE
'other'
END) age,
(CASE
WHEN age BETWEEN 13 AND 18 THEN
'kid'
WHEN age BETWEEN 19 AND 25 THEN
'bigger_kid'
WHEN age BETWEEN 26 AND 35 THEN
'adult'
WHEN age BETWEEN 36 AND 45 THEN
'bigger_adult'
WHEN age BETWEEN 46 AND 60 THEN
'big_adult'
WHEN age BETWEEN 61 AND 100 THEN
'caption'
ELSE
'other'
END) code,
(CASE
WHEN age BETWEEN 13 AND 18 THEN
'13-18'
WHEN age BETWEEN 19 AND 25 THEN
'19-25'
WHEN age BETWEEN 26 AND 35 THEN
'26-35'
WHEN age BETWEEN 36 AND 45 THEN
'36-45'
WHEN age BETWEEN 46 AND 60 THEN
'46-60'
WHEN age BETWEEN 61 AND 100 THEN
'61-100'
ELSE
'other'
END) age_interval,
SUM(total) AS total
FROM u
GROUP BY u.age)
SELECT code,
MAX(age) AS age_level,
MAX(age_interval) AS age_interval,
SUM(total) AS total
FROM sec_survey_users su
LEFT JOIN u
ON su.user_id = u.id
WHERE su.survey_id = 'D3A21B1C2D8334C9E055824F7FFC5DF4'
GROUP BY code