sql中灵活运用join使代码更直观简洁，以及使用join... on 1=1计算分组百分比 |
您所在的位置：网站首页 › searchfailedR:1意思 › sql中灵活运用join使代码更直观简洁，以及使用join... on 1=1计算分组百分比 |
sql中灵活运用join使代码更直观简洁，以及使用join... on 1=1计算分组百分比
|
1.表a的结构如下：
最终结果如下：
（1）不使用join： ceshi0 as ( select order_phone_num, --需要注意的是无论count(*),count(time_period)还是其他字段，结果是一样的，因为group by根据的是order_phone_num, --除了order_phone_num这个字段，分组之后，其他字段下的数据都会合并在一起，无论这些数据是否有重复 count(*) as period_num, concat_ws('、', collect_list(time_period)) as time_period_all_past --如果出现重复不会去重，因为有可能两次购买都在同一个时间段 from add_payment_period group by order_phone_num ), ceshi as ( select order_phone_num, time_period, count(*) as num from add_payment_period group by order_phone_num, time_period ), ceshi1 as ( select app.order_phone_num, ce.num, cusu.period_num from add_payment_period app join ceshi ce on app.order_phone_num = ce.order_phone_num and app.time_period = ce.time_period join ceshi0 cusu on app.order_phone_num = cusu.order_phone_num group by app.order_phone_num, ce.num, cusu.period_num ), ceshi2 as ( select order_phone_num, time_period, CONCAT(cast((num) * 100 / (period_num) as DECIMAL(18, 2)), '%') as hundrend_per from ceshi1 group by order_phone_num, time_period, hundrend_per ), ceshi3 as ( select order_phone_num, -- cast(time_period as varchar(100)) + ' ' + cast(hundrend_per as varchar(100)) concat_ws(' ', time_period, hundrend_per) as time_hundrend --用concat_ws将表格两列连接起来 from ceshi2 ), cusumeption_time_percent as ( select order_phone_num, concat_ws('、', collect_list(time_hundrend)) as time_period_all --如果出现重复不会去重，因为有可能两次购买都在同一个时间段 from ceshi3 group by order_phone_num ),（2）使用join cusumeption_time_percent2 as ( select order_phone_num, concat_ws('、', collect_list(time_period_all_1)) as time_period_all from ( select a.order_phone_num, time_period, concat_ws(' ', time_period, CONCAT(cast((a1) * 100 / (b1) as DECIMAL(18, 2)), '%')) as time_period_all_1 from ( (select order_phone_num, time_period, count(*) as a1 from add_payment_period group by order_phone_num, time_period ) a join ( select count(*) as b1, order_phone_num from add_payment_period group by order_phone_num ) b on a.order_phone_num = b.order_phone_num ) ) group by order_phone_num )（2）和（1）实现相同的功能，但（2）语句更简洁 需要注意的是，join…on 1=1,类似于cross join，都可以求两张表的笛卡尔积，即使两张表没有相同的字段，也能将两张表关联起来 2.join … on 1 = 1 # 求分组后，每一组个数占分组前总条数百分比 select order_phone_num, concat(round(num/all_num,3),'%') from ( select order_phone_num from ( select order_phone_num, count(*) as num from a group by order_phone_num ) a_num join ( select count(*) as all_num from a ) a_all on 1 = 1 )其中如果a_num有n行，a_all表示分组前的行数数字，肯定只有一条，它们join…on 1=1，结果还是n行，相当于在a_num中加上了一个常数列，这一列所有数据都是all_num，所以每一行的num都有一个all_num相对应，这样就可以用除法：num/all_num，计算百分比了 |
需求如下： 求出time_period所占的比例
【本文地址】
今日新闻 |
推荐新闻 |