分组聚合补齐结果

分组聚合补齐结果

【问题】

c# 和 SQL 按类和按日期查询出数据,
比如有表 table1 数据如下
id,  type,   year
1    33      2014 下半年  
2    33      2014 下半年
3    33      2013 上半年
4    34      2013 上半年
要得到按 year 排序后按 type 排序,统计 year 数据,并且 year 没有的数据赋为 0,比如要得到如下结果:
year,              no,          type
2014 下半年      2             33
2014 上半年      0             33
2013 下半年      0             33
2013 上半年      1             33
2014 下半年      0             34
2014 上半年      0             34
2013 下半年      0             34
2013 上半年      1             34

【回答】

这种按固定序列对齐的运算,用 sql 写起来很麻烦,得用子查询拼出基准表再用 JOIN 去对齐,大概是这样:

select x._year, IFNULL(x._no,0), x._type from (

select a._year _year,b._no _no, a._type _type from (

with yt as (

select ‘2013 上半年’ _year from dual union all

select ‘2013 下半年’ _year from dual union all

select ‘2014 上半年’ _year from dual union all

select ‘2014 下半年’ _year from dual

),tt as (

select ‘33’ _type from dual union all

select ‘34’ _type from dual

) select yt._year, tt._type from yt join tt on 1 = 1

)a

left join (

select _year , count(_type) ‘_no’, ‘33’ as ‘_type’ from test5 where _type=33 group by _year

union all

select _year , count(_type) ‘_no’, ‘34’ as ‘_type’ from test5 where _type=34 group by _year

)b

on a._year = b._year and a._type = b._type) x

而用 SPL 就要简单很多,可以用循环分步算出结果来:

A
1 =connect(“db”)
2 =query(“select _type,_year from test5”)
3 =A2.id(left(_year,4)).conj([+“上半年”,+“下半年”])
4 =A2.group(_type)
5 =A4.news(A3;:year,A4..count(year==~._year):no,A4._type:type)

A1: 连接 db 数据库。

A2:查询原表。

A3:获取年份数字并拼出上半年、下半年的字样。id 函数从序列中获取不重复的值,left 函数是取某个字段值从左数的 n 个字符的字符串,conj 函数对含有序列作为成员的序列 A 进行和列计算,使得他们成为一个序列。这里 A2.id(left(_year,4)) 是一个序列,计算结果是序列的成员分别加“上半年”和“下半年”字符串。

A4:根据 A2 中的 _type 分组。

A5:对 A4 每组序表执行 news 函数,依据 A3 年份计算每组序表的字段值合并生成新序表。首个字段值为 ~,是相对参数 A3 的,作为年份,取名为 year;第二个字段值是由 A4 的成员也就是分组后的各个成员序表,计数而来,条件是其年份字段值与 year 字段值相同,取名为 no;第三个字段是分组类型,直接用 A4 的 _type 字段值代替就可以,取名为 type。

A2

imagepng

A3

imagepng

A4

imagepng

A5

imagepng