wordpress代码分析,张店专业网站优化哪家好,wordpress用户只能一年内使用,万网虚拟机怎么做两个网站目录
准备工作#xff1a;
在hive中建表
在presto中计算
分解式
按照城市分组 统计人数
按照性别分组 统计人数
编辑
按照爱好分组 统计人数
编辑
按照城市和性别分组 统计人数
按照城市和爱好分组 统计人数
按照性别和爱好分组 统计人数
按照城市和性别还有…目录
准备工作
在hive中建表
在presto中计算
分解式
按照城市分组 统计人数
按照性别分组 统计人数
编辑
按照爱好分组 统计人数
编辑
按照城市和性别分组 统计人数
按照城市和爱好分组 统计人数
按照性别和爱好分组 统计人数
按照城市和性别还有爱好分组 统计人数
统计人数
合并式
presto使用grouping
presto使用grouping sets
grouping作用例子展示
高级用法: cube
rollup 用法 准备工作
在hive中建表
drop database if exists db_test cascade;create database db_test;create table db_test.tb_student(name string,score int,city string,sex string,hobby string
)
row format delimited fields terminated by \t;load data local inpath /test/student.txt into table db_test.tb_student;select * from db_test.tb_student;
student.txt数据 张三 10 北京 男 喝酒 李四 20 北京 男 抽烟 王五 30 北京 女 烫头 赵六 40 上海 男 抽烟 麻七 50 上海 女 烫头 在presto中计算 分解式
按照城市分组 统计人数
select city,count(1) as cnt from hive.db_test.tb_student group by city; 按照性别分组 统计人数
select hobby,count(1) as cnt from hive.db_test.tb_student group by hobby; 按照爱好分组 统计人数
select hobby,count(1) as cnt from hive.db_test.tb_student group by hobby; 按照城市和性别分组 统计人数
select city, sex, count(1) as cnt from hive.db_test.tb_student group by city, sex; 按照城市和爱好分组 统计人数
select city, hobby, count(1) as cnt from hive.db_test.tb_student group by city, hobby; 按照性别和爱好分组 统计人数
select sex, hobby, count(1) as cnt from hive.db_test.tb_student group by sex, hobby; 按照城市和性别还有爱好分组 统计人数
select city, sex, hobby, count(1) as cnt from hive.db_test.tb_student group by city, sex, hobby; 统计人数
select count(1) as cnt from hive.db_test.tb_student group by ();合并式
with t1 as (select city, null as sex, null as hobby, count(1) as cnt, 1 as o from hive.db_test.tb_student group by cityunion allselect null as city, sex, null as hobby, count(1) as cnt, 2 as o from hive.db_test.tb_student group by sexunion allselect null, null, hobby,count(1) as cnt, 3 as o from hive.db_test.tb_student group by hobbyunion allselect city, sex, null, count(1) as cnt, 4 as o from hive.db_test.tb_student group by city, sexunion allselect city, null, hobby, count(1) as cnt, 5 as o from hive.db_test.tb_student group by city, hobbyunion allselect null, sex, hobby, count(1) as cnt, 6 as o from hive.db_test.tb_student group by sex, hobbyunion allselect city, sex, hobby, count(1) as cnt, 7 as o from hive.db_test.tb_student group by city, sex, hobbyunion allselect null, null, null, count(1) as cnt, 8 as o from hive.db_test.tb_student group by ()
)
select * from t1
order by o, city, sex, hobby
; presto使用grouping
selectcity,sex,count(1) as cnt,grouping(city, sex) as g
from hive.db_test.tb_student
group by city, sex
; presto使用grouping sets
selectcity,sex,hobby,count(1) as cnt,grouping(city, sex, hobby)
from hive.db_test.tb_student
group by grouping sets (city, sex, hobby)
; selectcity,sex,hobby,count(1) as cnt,grouping(city, sex, hobby)
from hive.db_test.tb_student
group by grouping sets (city, sex, hobby, (city, sex), (city, hobby), (sex, hobby), (city, sex, hobby), ())
; selectcity,sex,hobby,count(1) as cnt,casewhen grouping(city, sex, hobby)3 then 1when grouping(city, sex, hobby)5 then 2when grouping(city, sex, hobby)6 then 3when grouping(city, sex, hobby)1 then 4when grouping(city, sex, hobby)2 then 5when grouping(city, sex, hobby)4 then 6when grouping(city, sex, hobby)0 then 7when grouping(city, sex, hobby)7 then 8else 100end as o
from hive.db_test.tb_student
group by grouping sets (city, sex, hobby, (city, sex), (city, hobby), (sex, hobby), (city, sex, hobby), ())
order by o, city, sex, hobby
; grouping作用例子展示
with t1 as (select 北京 as city, 男 as sexunion allselect 北京 as city, 男 as sexunion allselect 北京 as city, 女 as sexunion allselect 北京 as city, null as sex
)
selectcity,sex,count(1) as cnt
from t1
group by grouping sets (city, (city, sex)) 问题:city北京, sexnull, cnt4city北京, sexnull, cnt1为什么 city 和 sex 的值一样, 但是结果不同?
原因:一个null 表示跟这一列没有关系另一个null 表示 这一列的值 为null, 根据 列值统计的结果怎么区分
解决方案:grouping(city, sex)0,0 两个都有关0,1 只跟city有关1,0 只跟sex有关1,1 都这两列都无关 with t1 as (select 北京 as city, 男 as sexunion allselect 北京 as city, 男 as sexunion allselect 北京 as city, 女 as sexunion allselect 北京 as city, null as sex
)
selectcity,sex,count(1) as cnt,grouping(city, sex) g
from t1
group by grouping sets (city, (city, sex)) selectcity,sex,hobby,count(1) as cnt,casewhen grouping(city, sex, hobby)3 then 1when grouping(city, sex, hobby)5 then 2when grouping(city, sex, hobby)6 then 3when grouping(city, sex, hobby)1 then 4when grouping(city, sex, hobby)2 then 5when grouping(city, sex, hobby)4 then 6when grouping(city, sex, hobby)0 then 7when grouping(city, sex, hobby)7 then 8else 100end as o
from hive.db_test.tb_student
group by grouping sets (city, sex, hobby, (city, sex), (city, hobby), (sex, hobby), (city, sex, hobby), ())
order by o, city, sex, hobby 高级用法: cube selectcity,sex,hobby,count(1) as cnt,casewhen grouping(city, sex, hobby)3 then 1when grouping(city, sex, hobby)5 then 2when grouping(city, sex, hobby)6 then 3when grouping(city, sex, hobby)1 then 4when grouping(city, sex, hobby)2 then 5when grouping(city, sex, hobby)4 then 6when grouping(city, sex, hobby)0 then 7when grouping(city, sex, hobby)7 then 8else 100end as o
from hive.db_test.tb_student
group by cube(city, sex, hobby)
order by o, city, sex, hobby rollup 用法 selectcity,sex,hobby,count(1) as cnt,casewhen grouping(city, sex, hobby)3 then 1when grouping(city, sex, hobby)5 then 2when grouping(city, sex, hobby)6 then 3when grouping(city, sex, hobby)1 then 4when grouping(city, sex, hobby)2 then 5when grouping(city, sex, hobby)4 then 6when grouping(city, sex, hobby)0 then 7when grouping(city, sex, hobby)7 then 8else 100end as o
from hive.db_test.tb_student
group by rollup(city, sex, hobby)
order by o, city, sex, hobby
; 总结 presto时间函数 date()类型 表示 年月日 timestamp类型表示 年月日时分秒 egtimestamp2024-08-18 22:13:10,%Y-%m-%d %H%i%s date_add(unit, value,timestamp) grouping sets()相当于一个集合 都能根据括号里的内容分组查询到相应的数据 grouping 根据8421码 0表示与该列有关系1表示无关 通过计算数值 查看与列之间分组的关系 cube(city, sex, hobby) 等价于 grouping sets (city, sex, hobby, (city, sex), (city, hobby), (sex, hobby), (city, sex, hobby), ()) rollup (city, sex, name) 等价于 grouping set((city, sex, name), (city, sex), city, ())
