自主做网站,可视化网页制作,做网站功能,怎么看网站点击量有这么一个需求#xff0c;要对某个表的某列#xff0c;按累计分组计数汇总输出。 比如#xff1a;表t列a的数据如下#xff1a; ┌───┐ │ a │ ├───┤ │ 1 │ │ 2 │ │ 3 │ │ 1 │ │ 4 │ └───┘ 现在要把a1、a2、a3、a4的个数…有这么一个需求要对某个表的某列按累计分组计数汇总输出。比如表t列a的数据如下┌───┐ │ a │ ├───┤ │ 1 │ │ 2 │ │ 3 │ │ 1 │ │ 4 │ └───┘现在要把a1、a2、a3、a4的个数分别汇总输出得到如下的结果┌───┬──────┐ │ a │ cnt2 │ ├───┼──────┤ │ 1 │ 5 │ │ 2 │ 3 │ │ 3 │ 2 │ │ 4 │ 1 │ └───┴──────┘下面以duckdb数据库为例把代码稍作修改也能在postgresql上实现。方法1用case when分组和分析函数sum累计先建立表tcreate table t as (select * from values (1), (2), (3), (1), (4) t(a));在postgresql中需要把values子句包含在一对小括号中即create table t as (select * from (values (1), (2), (3), (1), (4)) t(a));然后输入以下查询select case when a1 then 1 when a2 then 2 when a3 then 3 when a4 then 4 end x, count(1) cnt from t group by x;得到┌───┬─────┐ │ x │ cnt │ ├───┼─────┤ │ 1 │ 5 │ └───┴─────┘这不是我们需要的结果因为case when 有短路的性质满足前面某一个条件(case)的统计以后就不再判断以后的条件。这样因为所有行都满足a1所以全部行都被统计到了x1的分组而其他分组没有再被统计。容易想到把条件从4到1倒序排列可以求出满足每个分组的计数。select case when a4 then 4 when a3 then 3 when a2 then 2 when a1 then 1 end x, count(1) cnt from t group by x; ┌───┬─────┐ │ x │ cnt │ ├───┼─────┤ │ 1 │ 2 │ │ 2 │ 1 │ │ 3 │ 1 │ │ 4 │ 1 │ └───┴─────┘这仍然不是我们要求的结果因为case when 仍然存在短路问题这种写法实际上隐含地滤掉了同时满足前一个条件的结果比如这里的when a3实际上是when a3 and a4不包含a4的结果因为a4的行在前一个条件when a4已经被统计就不会再次被统计。需要把这个结果再次累计汇总才能得到要求的结果。with t2 as( select case when a4 then 4 when a3 then 3 when a2 then 2 when a1 then 1 end x, count(1) cnt from t group by x) select x,cnt,sum(cnt)over(order by x desc)cnt2 from t2 order by x; ┌───┬─────┬──────┐ │ x │ cnt │ cnt2 │ ├───┼─────┼──────┤ │ 1 │ 2 │ 5 │ │ 2 │ 1 │ 3 │ │ 3 │ 1 │ 2 │ │ 4 │ 1 │ 1 │ └───┴─────┴──────┘为了明显起见这个查询保留了原查询的cnt和通过sum(cnt)over(order by x desc)新汇总的cnt2两列这个分析函数写法的含义是按照x从大到小的顺序即逆序 desc对cnt列累计求和这样cnt2列a4累计的结果就是原查询a4的cnt值保持现状a3累计的结果就是上一步a4累计值和a3的cnt值之和a2累计的结果就是上一步a3累计值和a2的cnt值之和以此类推。所以x和cnt2列就是所需的结果。方法2利用多个case when打分组标记然后通过标记的存在性统计先看打完标记的情况select case when a1 then 1 else a end || case when a2 then 2 else a end || case when a3 then 3 else a end || case when a4 then 4 else a end x, count(1) cnt from t group by x; ┌──────┬─────┐ │ x │ cnt │ ├──────┼─────┤ │ 1aaa │ 2 │ │ 12aa │ 1 │ │ 123a │ 1 │ │ 1234 │ 1 │ └──────┴─────┘在这个结果中x列现在包含一个字符串其中用字符n标出了满足第n个条件cnt列的的统计结果与前一种方法第一步的中间结果一致然后针对这个结果用sum(case when)方法凡是出现字符1的都被统计到1组出现字符2的都被统计到2组以此类推。为了防止出现短路现象用了一个包含1到4的临时表做笛卡尔积把临时表中出现每个字符都和上述x字符串进行比较这样包含字符串1的第14行都被统计到1组包含字符串2的第24行都被统计到2组以此类推实现了累计的效果。在postgresql中对列的类型一致性要求更严格需要把代码1、2、3、4用单引号括起来表示字符类型。查询语句和结果如下with t2 as ( select case when a1 then 1 else a end || case when a2 then 2 else a end || case when a3 then 3 else a end || case when a4 then 4 else a end x, count(1) cnt from t group by x) select a, sum(case when instr(x,a::varchar)0 then cnt end)cnt2 from t2,values(1),(2),(3),(4) t3(a) group by a order by a; ┌───┬──────┐ │ a │ cnt2 │ ├───┼──────┤ │ 1 │ 5 │ │ 2 │ 3 │ │ 3 │ 2 │ │ 4 │ 1 │ └───┴──────┘在postgresql中要用strpos代替instr。方法3利用码表笛卡尔积对代码分组在实现上述方法2的过程中考虑到第一步把x值转变成代码第二步从代码判断能否合成一步呢从而有如下的思路先建立一张码表code列出每个代码表示的上下限然后就省去了打标记的步骤直接根据x的值统计即可。with code(c,low,high) as( values (1,1,9999), (2,2,9999), (3,3,9999), (4,4,9999) ) select c, count(case when alow and ahigh then 1 end)cnt2 from t,code group by c order by c; ┌───┬──────┐ │ c │ cnt2 │ ├───┼──────┤ │ 1 │ 5 │ │ 2 │ 3 │ │ 3 │ 2 │ │ 4 │ 1 │ └───┴──────┘上述语句中上限9999是一个超出范围的大数使得ahigh永远为真可根据实际需求调整比如下列查询得到不累计的计数with code(c,low,high) as( values (1,1,2), (2,2,3), (3,3,4), (4,4,9999) ) select c, count(case when alow and ahigh then 1 end)cnt2 from t,code group by c order by c; ┌───┬──────┐ │ c │ cnt2 │ ├───┼──────┤ │ 1 │ 2 │ │ 2 │ 1 │ │ 3 │ 1 │ │ 4 │ 1 │ └───┴──────┘方法4用union all合并多个条件汇总结果select 1 x, count(1) cnt from t where a1 union all select 2 x, count(1) cnt from t where a2 union all select 3 x, count(1) cnt from t where a3 union all select 4 x, count(1) cnt from t where a4 ; ┌───┬─────┐ │ x │ cnt │ ├───┼─────┤ │ 1 │ 5 │ │ 2 │ 3 │ │ 3 │ 2 │ │ 4 │ 1 │ └───┴─────┘完全用手工统计每种条件的结果然后把结果合并是最容易的方法。