英文:
Group by multiple columns and add them
问题
I have a table something like below
level1 | level2 | level3 | key |
---|---|---|---|
A | B | C | k1 |
A | B | C | K2 |
A | B | D | k2 |
A | B | E | k3 |
A | F | G | k4 |
A | F | null | k5 |
A | null | null | k6 |
A | B | null | k7 |
A | B | null | k8 |
I am expecting to carry hierarchy information to child count like below
A-->1
A-->B -->2+1(a)=3
A-->B--->C--->1+2+2( 2 entries for ABC)=5
A--B-->D--->1+2+1=4
A--B-->E--->1--->1+2+1=4
A--F--->1+1=2
A--F--->G--->1+1+1=3
My tried the query below does not give the right result
select CONCAT(ifnull(level1, ''), ' > ', ifnull(level2, ''), ' > ', ifnull(level3, '')) as 'foo',COUNT(*) as 'count' from table t t.level1, t.level2, t.level3;
How can I apply the aggregate function and fetch the right result.
英文:
I have a table something like below
level1 | level2 | level3 | key |
---|---|---|---|
A | B | C | k1 |
A | B | C | K2 |
A | B | D | k2 |
A | B | E | k3 |
A | F | G | k4 |
A | F | null | k5 |
A | null | null | k6 |
A | B | null | k7 |
A | B | null | k8 |
I am expecting to carry hierarchy information to child count like below
A-->1
A-->B -->2+1(a)=3
A-->B--->C-->1+2+2( 2 entries for ABC)=5
A--B-->D-->1+2+1=4
A--B-->E-->1-->1+2+1=4
A--F-->1+1=2
A--F-->G-->1+1+1=3
My tried the query below does not give the right result
select CONCAT(ifnull(level1, ''), ' > ', ifnull(level2, ''), ' > ', ifnull(level3, '')) as 'foo',COUNT(*) as 'count' from table t t.level1, t.level2, t.level3;
How can I apply the aggregate function and fetch the right result.
答案1
得分: 1
这个查询给了我想要的结果:
with g as (
select level1 l1, level2 l2, level3 l3, count(1) cnt
from mytable group by level1, level2, level3)
select concat(g3.l1,
case when g3.l2 is null then '' else concat('>', g3.l2) end,
case when g3.l3 is null then '' else concat('>', g3.l3) end) path,
case when g3.l3 is null then g3.cnt
else g1.cnt +g2.cnt + g3.cnt end cnt
from g g3
left join g g1 on g1.l2 is null and g3.l3 is not null and g3.l1 = g1.l1
left join g g2 on g2.l2 is not null and g2.l3 is null and g3.l3 is not null
and g3.l1 = g2.l1 and g3.l2 = g2.l2
除了 AFG 应该是 3 (A 一次 + AF 一次 + AFG 一次)?
查询首先对值进行分组,然后将所有出现的情况 g3
自连接到两个出现的情况 g2
和一个出现的情况 g1
,并按要求呈现。
英文:
This query gave me desired result:
with g as (
select level1 l1, level2 l2, level3 l3, count(1) cnt
from mytable group by level1, level2, level3)
select concat(g3.l1,
case when g3.l2 is null then '' else concat('>', g3.l2) end,
case when g3.l3 is null then '' else concat('>', g3.l3) end) path,
case when g3.l3 is null then g3.cnt
else g1.cnt +g2.cnt + g3.cnt end cnt
from g g3
left join g g1 on g1.l2 is null and g3.l3 is not null and g3.l1 = g1.l1
left join g g2 on g2.l2 is not null and g2.l3 is null and g3.l3 is not null
and g3.l1 = g2.l1 and g3.l2 = g2.l2
except for AFG which should be 3? (A once + AF once + AFG once).
Query groups values at first, then self joins all occurences g3
with two occurences g2
and one occurence g1
and presents as requested.
答案2
得分: 1
请尝试以下操作:
with concat_levels as
(
select concat_ws('>', level1, level2) lvl_1_2,
concat_ws('>', level1, level2, level3) lvl_1_2_3, count(*) cn
from table_name
group by 1, 2
)
select t1.lvl_1_2_3 as levels,
t1.cn + coalesce(sum(t2.cn), 0) as res
from concat_levels t1 left join concat_levels t2
on t1.lvl_1_2_3 like (concat(t2.lvl_1_2, '%')) and
length(t1.lvl_1_2_3) > length(t2.lvl_1_2_3)
group by t1.lvl_1_2_3, t1.cn
order by t1.lvl_1_2_3
英文:
Try the following:
with concat_levels as
(
select concat_ws('>', level1, level2) lvl_1_2,
concat_ws('>', level1, level2, level3) lvl_1_2_3, count(*) cn
from table_name
group by 1, 2
)
select t1.lvl_1_2_3 as levels,
t1.cn + coalesce(sum(t2.cn), 0) as res
from concat_levels t1 left join concat_levels t2
on t1.lvl_1_2_3 like (concat(t2.lvl_1_2, '%')) and
length(t1.lvl_1_2_3) > length(t2.lvl_1_2_3)
group by t1.lvl_1_2_3, t1.cn
order by t1.lvl_1_2_3
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论