- 博客(37)
- 收藏
- 关注
RSS订阅原创 计算用户的最大活跃日期间隔
有两种实践方式，第一种xf大神实现的，我采用该种方式1、krill建表，取一个城市观察，南京，注册时间<11月，11月的用户活跃数，CREATE table pri_da_risk.dmh_app_open_nj_11 as SELECT DISTINCT a1.passenger_id ,a1.dsFROM (SELECT passenger_id,ds FROM pub_cockpit_db.ods_user_app_open_detail WHERE ds >=
2020-12-25 14:43:50
328
原创 查询在一张表不在另外一张表的记录
假设有表A和B，字段都是passenger_id，那么取在A表中包含，但是在B表中不包含的id的方法如下SELECT A.IDFROM ALEFT JOIN BON A.id = B.idWHERE B.id is NULL原因：left join取左表所有记录，以及右表B中和左表重复的记录，因此，A表有而B表无的记录，会记录B.id 为NULL。因此，针对需求，限制条件WHERE B.id is NULL，将得到A表有，B表无的记录。...
2020-12-23 16:39:08
619
原创 新老用户完单代码
jh优化了我的代码，优化后极为漂亮一定要分享给大家代码太长，每次复制太长的代码在文章中，总是弹出内容为空，不知道为啥，那我就分段写吧SELECT '${end_date}' AS ds ,COUNT(DISTINCT passenger_id) AS 完单总用户数 ,COUNT(DISTINCT CASE WHEN user_tag='newer' THEN pass
2020-12-09 14:17:45
115
原创 沙箱的一个动态写法
今天学习到了一个沙箱的动态时间写法如下SELECT passenger_id ,COUNT(DISTINCT route_id) AS route_cnt FROM pub_cockpit_db.dws_ord_route_p_detail_fact_ds WHERE ds>=from_unixtime(unix_timestamp('${start_date}'),'yyyyMMdd') AND ds<=from_unixtime(unix_time
2020-12-09 13:58:06
153
原创 一段优雅的代码
今天看了xp的一段代码，叹为观止，---完单复购率SELECT t.ds '日期', t.finish_cnt '当日完单用户数', t.finish_cnt_1/t.finish_cnt as '次1日完单率', t.finish_cnt_2/t.finish_cnt as '次2日完单率', t.finish_cnt_3/t.finish_cnt as '次3日完单率', t.finish_cnt_4/t.finish_cnt as '次4日完单率', t.finish_cn
2020-11-25 15:04:39
154
1
原创 2020-11-25
以下作为流失率分子，不应作为分母，因为最近一次完单时间已经限制为30-60天，这个条件包含了最近30天没有消费，COUNT(DISTINCT CASE WHEN last_order_time >= date_sub(from_unixtime(unix_timestamp(ds,'yyyyMMdd'),'yyyy-MM-dd'),60) AND last_order_time <= date_sub(from_unixtime(unix_timestamp(ds,'yyyyMM
2020-11-25 15:03:31
46
原创 20201124格式转换为2020-11-24
ds=20201124，如何转换为2020-11-24格式呢，concat(substr(ds,1,4),'-',substr(ds,5,2),'-',substr(7,2))，这种报错以下为正确写法from_unixtime(unix_timestamp(ds,'yyyyMMdd'),'yyyy-MM-dd')以下取30天流失率的分母SELECT a1.ds ,COUNT(DISTINCT a1.passenger_id) AS fenzi ,COUNT(DISTINCT a2.p
2020-11-25 10:18:57
434
原创 2020-11-19
核查承影看板和沙箱的数据，请教测试NB，get到了不少新东西，是为记录。1、dws_ord_route_p_detail_fact_ds，从该表取在2020118完单的正确写法如下注①：为什么不写ds？因为该表虽然有ds，但是kudu表，是按照用户id分区，不是按照ds分区（hive表按照ds分区）。注②：完单条件为什么不写cancel_time is null?因为该表从底层表取数时，settlement_time已经包含cancel_time is null条件，因此，该条件可以不写。注③为什.
2020-11-19 18:40:27
120
原创 注册、注册且t+6冒泡的新写法
取新用户注册用户数，新用户注册后T+6冒泡用户数的写法，原来是比较复杂的写法，今天首先使用下面，但是结果错误。结果注册用户数=冒泡用户数SELECT STRAIGHT_JOIN register_date ,count(distinct a1.passenger_uuid) AS cnt_register --注册用户数 ,count(distinct a3.passenger_id) AS cnt_bubbler --冒泡用户数FROM (SELECT pass
2020-11-16 17:05:34
122
原创 取老用户数的方法
SELECT weekofyear(to_timestamp(ds,'yyyyMMdd')) AS week ,ds ,COUNT(Passenger_uuid) AS yx_Passenger_nmFROM Pub_cockpit_db.Dm_user_basic_char_dtWHERE ds >= from_unixtime(unix_timestamp( date_add(now(),-(dayofweek(now())-2))
2020-11-13 16:12:25
68
原创 沙箱报错
代码在impala可以跑，在krill报错java.lang.reflect.UndeclaredThrowableException原因：1、代码中的[shuffle],interval,STRAIGHT JOIN是impala专有语法，在krill写法是不一样的，2、krill中 中文别名需要‘中文’，如下a.id AS ‘用户代码’以下写法错误a.id AS '用户代码'欢迎使用Markdown编辑器你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使
2020-11-12 15:24:20
645
原创 老客新客的新写法
今天检查老客户写法的误差，耗费无数心血，终于解决了。原来的方法：取注册时间<完单时间所在的星期一，but结果总是有三万左右误差新方法：取老客户注册时间所在周 < 完单时间所在周新客户：注册时间所在周 = 完单时间所在周SELECT b.week1 AS week ,COUNT(DISTINCT b.Passenger_id) AS Passenger_finish_cnt ---老用户当周完单人数 ,'全国'
2020-11-11 15:14:08
140
原创 from_unixtime(unix_timestamp( date_add(now(),-(dayofweek(now())-2))- interval 7 weeks ),‘yyyyMMdd‘
from_unixtime(unix_timestamp( date_add(now(),-(dayofweek(now())-2))- interval 7 weeks ),‘yyyyMMdd’)一个有意思的函数（今天巨不开心，一个努力了很久，期待了很久的项目，收到了失败的消息，难受，火锅也不想吃了，吃啥都不香了，只想学习来忘记烦恼）本周一from_unixtime(unix_timestamp( date_add(now(),-(dayofweek(now())-2)) ),'yyyyMMdd'
2020-11-11 12:32:29
298
1
原创 驾驶舱
取新注册用户的完单数据，和驾驶舱数据差异较大我的完单数据怎么取得呢SELECT distinct passenger_id ,weekofyear(to_timestamp(settlement_time,'yyyy-MM-dd')) AS week FROM pub_cockpit_db.dws_ord_route_p_detail_fact_ds WHERE ds >= from_unixtime(unix_timestamp( date_add(now(),-(dayof
2020-11-10 18:20:31
195
原创 增加数据库
今天在沙箱里跑一个代码，impala出错，hive可以跑，但是承影的数据库设定的是impala，请假clf小可爱，小可爱告诉我一个惊人的方法，那就是，当当当，可以在数据库里添加新的数据库，我操作了一下，成功了，可以同时添加多个数据库，这个功能好帅，超级棒...
2020-11-10 11:18:43
72
原创 2020-11-09
用户漏斗代码遇到一个代码优化的问题取近8周的时间周期，分别取每周的注册用户数/上周注册用户数/上上周注册用户/上上周前注册用户，代码如下，后又与活跃、冒泡、呼叫、接起、完单表连接，代码太复杂，导致沙箱超时挂起。原注册用户代码如下怎样优化呢，主要是本周/上周/上上周/上上周前，写法复杂。高手在哪里SELECT a1.week AS analy_clycle ,CASE WHEN weekofyear(to_timestamp(a1.ds,'
2020-11-09 16:23:17
41
原创 一个神奇的函数，可以取到每个星期一
今天学习到了一个神奇的函数，可以取到任意ds所在的星期一，星期二，一直到星期日相信他会很有用武之地ds所在的星期一的取法如下date_sub(next_day(to_date( from_unixtime(unix_timestamp(ds,'yyyyMMdd'),'yyyy-MM-dd') ),'MO'),5) 更神奇的是，这里的MO，是Monday，因此，以下两个函数同样表示每个ds所在的星期一date_sub(next_day(to_date( from_unixtime(unix_tim
2020-11-04 15:51:32
581
原创 每周一
麦肯锡仪表盘蒋老师反馈，和驾驶舱数据不一致。因为我是用dm的表，驾驶舱数据来自ads的表香平讲，dm和ads的表数据确实不一致，以ads为准因为我需要重新使用ads表写老客户WAU及活跃度每个程序员的代码就像自己的孩子，第一版代码即用dm表统计的代码放上来，备份下，但是，上传不了...
2020-11-04 14:50:23
65
原创 空值≠is nu‘l‘l
今天学到了一个小点，下面两段代码结果不同，想一想为什么为什么呢，你明白了么select * from A where id is nullselect * from A where id = ''
2020-11-02 19:20:50
87
原创 2020-11-02
老用户周人均完单量=老注册用户总完单量 / 老注册用户完单用户数老用户：注册时间在本周前的用户注：因为整个代码复制过来出现问题，因为一段的粘贴SELECT STRAIGHT_JOIN a1.week ,a1.city_name ,a1.route_id_num / a2.passenger_id_num ---老用户周度人均订单量FROM (SELECT STRAIGHT_JOIN c1.week AS week ,COUNT(
2020-11-02 16:37:21
51
原创 用户漏斗每日更新
用户漏斗数据每日都要更新这次优化使用shuffle，果然效率提高了一倍/*编写 人：me编写日期：2020.10.28修改日期：2020.11.2目 标：建设承影看板修改：1、增加上上周注册用户，2、呈现近8周的数据*/---------------------------分城市，近8周漏斗数据CREATE table pri_da_risk.dmh_mck_week_funnel_city_last_8week as (SELECT STRAIGHT_JOIN a5.ci
2020-11-02 12:52:15
133
原创 2020-11-02
更新承影中用户漏斗，发现沙箱建表时的代码丢失，我需要重新写。写完之后记录在此，防止丢失。新用户首两周5单占比SELECT n.week week ,n.pro pro ,case when n.register_city = '500100' then '重庆市' when n.register_city = '410100' then '郑州市' when n.register_city = '220100' then '长春市'
2020-11-02 10:32:40
65
原创 老用户注册用户数取法
遇到一个问题取每周的老用户注册用户数老用户为，注册时间小于本周的用户换一个思路，用本周末的注册用户数-本周新增注册用户数SELECT b1.City_name AS City_name ,MAX(b1.W_new_user_cnt) /10000 AS W_new_user_cnt ,b1.Week AS WeekFROM (SELECT City_name ,SUM(W_new_user_cnt) AS W_new_user_c
2020-11-02 09:43:09
98
原创 老用户注册用户数取法
遇到一个问题取每周的老用户注册用户数老用户为，注册时间小于本周的用户换一个思路，用本周末的注册用户数-本周新增注册用户数SELECT a1.Week ,a1.City_name ,(a1.W_au_cnt - a2.w_new_finish_cnt) ---老用户当周完单人数 ,(a1.W_au_cnt - a2.w_new_finish_cnt) / (a3.w_register_pcnt - a4.W_new_user_cnt) ---老用户活跃占比FROM
2020-10-30 20:20:52
101
原创 老用户WAU以及老用户周活跃度
今天是星期五，一个脑细胞耗得很厉害的星期五，今天完成麦肯锡的仪表盘需求，编写一个代码，反复出现错误，反复修改，沙箱和承影反复测试，代码保存下SELECT a1.Week ,a1.City_name ,(a1.W_au_cnt - a2.w_new_finish_cnt) ---老用户当周完单人数 ,(a1.W_au_cnt - a2.w_new_finish_cnt) / a3.w_register_pcnt ---老用户活跃占比FROM (SELECT WEEKOF
2020-10-30 14:10:27
269
原创 排序
排序函数写法SELECT t.passenger_id ,max(CASE WHEN t.rank_id =1 THEN end_charging_time else NULL END) AS last_end_charging_time ----最后一次完单时间 ,max(CASE WHEN t.rank_id =2 THEN end_charging_time else NULL END) AS last_two_end_charging_time ----倒
2020-10-30 09:53:22
61
原创 union all的错误用法
下面一段代码哪里错了呢找找看 SELECT *FROM (SELECT City_name,SUM(W_new_user_cnt) AS W_new_user_cnt,DsFROM Pub_cockpit_db.Ads_mob_dri_user_gather_wsWHERE Ds BETWEEN ‘20201001’ AND ‘20201010’AND City_name in (‘南京市’,‘杭州市’,‘武汉市’,‘长沙市’ ,‘广州市’,‘天津市’,‘重庆市’,‘长春市
2020-10-30 09:29:45
462
原创 承影&驾驶舱&维保平台培训
《承影培训》苏老师指标，要把维度带上承影的下钻功能环比同比，需要自己取计算 群文档，承影参数，ds取参数，怎么取， 在可视化视图——配置中，开启缓存，300s注：平台对手机号码脱敏，那么承影会对脱敏后相同的部分进行合并count《驾驶舱培训》崔老师驾驶舱，用户域无高德数据周用户活跃度：（期初+期末的活跃用户数）/ 2 * 期间全量活跃用户数7日用户流失率：当日完单用户且在其后7天没有完单的用户数/当日完单用户数7日复购率：当日完单，且在其后2-6天有完单的用户数/当日完
2020-10-29 17:44:24
74
原创 broadcast和shuffle，sql代码提效工具
参考来源： http://hbasefly.com/2017/03/19/sparksql-basic-join/1、背景： 在使用Impala进行SQL查询的时候，我们经常会使用join来关联多个表进行查询，获取想要的结果。对于表的数量达到千万甚至上亿的时候，不同的join方式所造成的执行速度，可能差距非常大。Impala提供了两种Join算法－shuffle和broadcast。2、主要Join方式 broadcast join适合大表与小表的join，将大表划分成多块，小表广播与这些块进行h
2020-10-29 13:49:30
776
空空如也
空空如也
TA创建的收藏夹 TA关注的收藏夹
TA关注的人