我有一个用于交叉表的SQL查询,以获得连续属性的每个bin范围的每个不同类别的计数。
例如:性别(类别属性男性,女性)和年龄(连续属性)。对于年龄,入库创建为,0-10,10-20,20-30,30-40...
因此,我正在寻找查询返回分类属性(性别)上的每个范围的计数。
预期输出:特定范围: 0-10
CNT LABEL
365503 MALE
177939 FEMALE下面的查询是有效的,但是一次只针对一个范围,所以我必须为每个范围放一个循环,
WITH q0 AS
(SELECT GENDER label
FROM IMPORTDATAAJ
WHERE GENDER IS NOT NULL
GROUP BY GENDER
ORDER BY 1 ASC
) ,
q1 AS
(SELECT GENDER label
FROM IMPORTDATAAJ
WHERE AGE >= 0
AND AGE <= 1.0
),
q3 AS
(SELECT q0.label label,
COUNT(q1.label) cnt
FROM q0
LEFT OUTER JOIN q1
ON (q0.label=q1.label)
GROUP BY q0.label
ORDER BY label
)
SELECT q3.label,q3.cnt FROM q3但我想删除这个循环,以提高性能,并希望一次获得所有范围。
发布于 2016-06-13 14:51:13
使用辅助表格,如下所示:
select (level-1)*10 low_limit,
level*10 high_limit ,
((level-1)*10) || ' - ' || (level*10) as band
from dual connect by level <= 12然后将其加入并按以下条件分组:
select band, gender, count(*)
from(
select gender as label
from IMPORTDATAAJ
join (select (level-1)*10 low_limit,
level*10 high_limit ,
((level-1)*10) || ' - ' || (level*10) as band
from dual connect by level <= 12
)
on age >=low_limit and age < high_limit
)
group by band, gender
order by band, gender;https://stackoverflow.com/questions/37782902
复制相似问题