sql 取一个月日期去掉周末的所有日期_ADS层（应用层）SQL总结之活跃统计

最新推荐文章于 2024-01-03 01:22:41 发布
最新推荐文章于 2024-01-03 01:22:41 发布
阅读量367 收藏 1
点赞数
文章标签： sql 取一个月日期去掉周末的所有日期 sql 求占比 sql求占比

674cf654cc62c77064f388fc7d2e2e52.png

首先注意一点在dwt层数据已经都经过了聚合，在ads层所有的id都是唯一存在的不需要再group by

dws要根据表来具体分析，如果是日活，不同分区很有可能有重复id

活跃设备数（日、周、月）

table ads_uv_count(
`dt` string COMMENT '统计日期',
`day_count` bigint COMMENT '当日用户数量',
`wk_count` bigint COMMENT '当周用户数量',
`mn_count` bigint COMMENT '当月用户数量',
`is_weekend` string COMMENT 'Y,N 是否是周末,用于得到本周最终结果',
`is_monthend` string COMMENT 'Y,N 是否是月末,用于得到本月最终结果'
) COMMENT '活跃设备数'

导入SQL

因为报表是每天产生的，假设今天是2020-04-28，所以dt='2020-04-28'

是否是星期天的判断if(date_add(next_day('2020-04-28','MO'),-1)='2020-04-28','Y','N')

即4-28的下一个周一再减一天是否是4-28，是即为周天

是否是月末判断if(last_day('2020-04-28')='if(last_day('2020-04-28')='2020-03-10','Y','N')','Y','N')

即当前日期当月的最后一天是否是今天，是即为月末

查每天即最后登录=今天

查每周即最后登录=<本周末 >=本周一

查每月即最后登录月=本月

insert into table ads_uv_count
select
'2020-04-28' dt,
daycount.ct,
wkcount.ct,
mncount.ct,
if(date_add(next_day('2020-04-28','MO'),-1)='2020-04-28','Y','N') ,
if(last_day('2020-04-28')='2020-04-28','Y','N')
from
(
    select
    '2020-04-28' dt,
    count(*) ct
    from dwt_uv_topic
    where login_date_last='2020-04-28'
)daycount join
(
    select
    '2020-04-28' dt,
    count (*) ct
    from dwt_uv_topic
    where login_date_last>=date_add(next_day('2020-04-28','MO'),-7)
    and login_date_last<= date_add(next_day('2020-04-28','MO'),-1)
) wkcount on daycount.dt=wkcount.dt
join
(
    select
    '2020-04-28' dt,
    count (*) ct
    from dwt_uv_topic
    where
    date_format(login_date_last,'yyyy-MM')=date_format('2020-04-28','yyyy-MM')
)mncount on daycount.dt=mncount.dt;

新增设备数

table ads_new_mid_count
(
`create_date` string comment '创建时间' ,
`new_mid_count` BIGINT comment '新增设备数量'
) COMMENT '每日新增设备信息数量'

导入SQL

很简单就是查今天'2020-04-28'是首次登录

insert into table ads_new_mid_count
select
'2020-04-28',
count(*)
from dwt_uv_topic
where login_date_first='2020-04-28'

沉默用户数

沉默用户：只在安装当天启动过，且启动时间是在7 天前

table ads_silent_count(
`dt` string COMMENT '统计日期',
`silent_count` bigint COMMENT '沉默设备数'
)

导入SQL

即首次登录即最后登录，且最后一次登录是7天前

insert into table ads_silent_count
select
'2020-04-28',
count(*)
from dwt_uv_topic
where login_date_first=login_date_last
and login_date_last<=date_add('2020-04-28',-7);

本周回流用户数

本周回流用户：上周未活跃，本周活跃的设备，且不是本周新增设备

table ads_back_count(
`dt` string COMMENT '统计日期',
`wk_dt` string COMMENT '统计日期所在周',
`wastage_count` bigint COMMENT '回流设备数'
)

导入SQL

分成两部分

第一部分查询dwt_uv_topic，在本周活跃且不是本周的新增设备id

第二部分查询dws_uv_detail_daycount，查出上周所有的设备id

本周活跃id left join 上周所有活跃id，右表为空的值即代表上周不活跃但是本周活跃

insert into table ads_back_count
select
'2020-04-28',
count(*)
from
(
    select
        mid_id
    from 
        dwt_uv_topic
    where login_date_last>=date_add(next_day('2020-04-28','MO'),-7)
    and login_date_last<= date_add(next_day('2020-04-28','MO'),-1)
    and login_date_first<date_add(next_day('2020-04-28','MO'),-7)
)current_wk
left join
(
    select
        mid_id
    from 
        dws_uv_detail_daycount
    where dt>=date_add(next_day('2020-04-28','MO'),-7*2)
    and dt<= date_add(next_day('2020-04-28','MO'),-7-1)
    group by mid_id
)last_wk
on current_wk.mid_id=last_wk.mid_id
where last_wk.mid_id is null;

流失用户数

流失用户：最近7 天未活跃的设备

table ads_wastage_count(
`dt` string COMMENT '统计日期',
`wastage_count` bigint COMMENT '流失设备数'
)

导入SQL

最后一次登录在七天前的用户

select
'2020-04-28',
count(*)
from dwt_uv_topic
where login_date_last<=date_add('2020-04-28',-7);

留存率

留存用户：某段时间内的新增用户（活跃用户），经过一段时间后，又继续使用应用的被认为是留存用户。

留存率：留存用户占当时新增用户（活跃用户）的比例即为留存率

d2c6916f4298f2411b3072f14f8c3da3.png 
table ads_user_retention_day_rate
(
`stat_date` string comment '统计日期',
`create_date` string comment '设备新增日期',
`retention_day` int comment '截止当前日期留存天数',
`retention_count` bigint comment '留存数量',
`new_mid_count` bigint comment '设备新增数量',
`retention_ratio` decimal(10,2) comment '留存率'
) COMMENT '每日用户留存情况'

统计近三天留存率，导入sql

首先明确相对日期2020-04-28

其次明确统计日期2020-04-27

求04-27的一天留存数，即4-27是首次登录，且4-28是末次登录

求4-27的新增数，即4-27是首次登录

前二者相除得最后结果

以此类推

。。。。。。

insert into table ads_user_retention_day_rate
select
    '2020-04-28',--统计日期
    date_add('2020-04-28',-1),--新增日期'2020-04-27'
    1,--留存天数
    sum(if(login_date_first=date_add('2020-04-28',-1) 
        and login_date_last='2020-04-28',1,0)),--2020-04-27 的1 日留存数
    sum(if(login_date_first=date_add('2020-04-28',-1),1,0)),--2020-04-27 新增
    sum(if(login_date_first=date_add('2020-04-28',-1) 
        and login_date_last='2020-04-28',1,0))/sum(if(login_date_first=date_add('2020-04-28',-
        1),1,0))*100-- 2020-04-27 相对 2020-04-28 1天留存率
from dwt_uv_topic
union all
select
    '2020-04-28',--统计日期
    date_add('2020-04-28',-2),--新增日期
    2,--留存天数
    sum(if(login_date_first=date_add('2020-04-28',-2) and
        login_date_last='2020-04-28',1,0)),--2020-04-26 的2 日留存数
    sum(if(login_date_first=date_add('2020-04-28',-2),1,0)),--2020-04-26 新增
    sum(if(login_date_first=date_add('2020-04-28',-2) and
        login_date_last='2020-04-28',1,0))/sum(if(login_date_first=date_add('2020-04-28',-
        2),1,0))*100-- 2020-04-26 相对 2020-04-28 2天留存率
from dwt_uv_topic
union all
select
    '2020-04-28',--统计日期
    date_add('2020-04-28',-3),--新增日期
    3,--留存天数
    sum(if(login_date_first=date_add('2020-04-28',-3) and
        login_date_last='2020-04-28',1,0)),--2020-04-25 的3 日留存数
    sum(if(login_date_first=date_add('2020-04-28',-3),1,0)),--2020-04-25 新增
    sum(if(login_date_first=date_add('2020-04-28',-3) and
        login_date_last='2020-04-28',1,0))/sum(if(login_date_first=date_add('2020-04-28',-
        3),1,0))*100-- 2020-04-25 相对 2020-04-28 2天留存率
from dwt_uv_topic;

最近连续三周活跃用户数

table ads_continuity_wk_count(
`dt` string COMMENT '统计日期,一般用结束周周日日期,如果每天计算一次,可用当天日
期',
`wk_dt` string COMMENT '持续时间',
`continuity_count` bigint COMMENT '活跃次数'
)

导入sql

要求连续三周活跃，则每周都要有活跃，这需要一个设备的历史数据

历史数据dwt层不存在，只有dws层有

要连续三周活跃，则分解成近三周每周都活跃

先去重得到最近三周的活跃设备（记得求每周都要group by，因为dws_uvdetail_daycount表的细粒度只到天级，本周很可能有重复mid_id）

union all 三张表得到三周活跃设备信息（未去重，这时候的细粒度来到了周级，而三周的规模表很可能存在重复）

去重并加上条件having count(*)=3得到三周都存在的mid_id

对结果进行count(*),得到最后结果

insert into table ads_continuity_wk_count
select
    '2020-04-29',
    concat(date_add(next_day('2020-04-29','MO'),-7*3),'_',date_add(next_day('
    2020-04-29','MO'),-1)),
    count(*)
from
(
    select
        mid_id
    from
    (
        select
            mid_id
        from dws_uv_detail_daycount
        where dt>=date_add(next_day('2020-04-29','monday'),-7)
        and dt<=date_add(next_day('2020-04-29','monday'),-1)
        group by mid_id
        
        union all
        select
            mid_id
        from dws_uv_detail_daycount
        where dt>=date_add(next_day('2020-04-29','monday'),-7*2)
        and dt<=date_add(next_day('2020-04-29','monday'),-7-1)
        group by mid_id
        
        union all
        select
            mid_id
        from dws_uv_detail_daycount
        where dt>=date_add(next_day('2020-04-29','monday'),-7*3)
        and dt<=date_add(next_day('2020-04-29','monday'),-7*2-1)
        group by mid_id
    )t1
    group by mid_id
    having count(*)=3
)t2

最近七天内连续三天活跃用户数

table ads_continuity_uv_count(
`dt` string COMMENT '统计日期',
`wk_dt` string COMMENT '最近7 天日期',
`continuity_count` bigint
) COMMENT '连续活跃设备数'

导入sql

这是一个多层嵌套sql，我们一层一层往外解析

第一层通过dws_uv_detail_daycount求出最近7天所有活跃用户

通过开窗函数根据mid_id分区做rank（这里不会出现rank重复，因为dws已在天级细粒度实现去重），时间升序排序

第二层对dt和rk做一个差值，这样连续的天数相减会得到一个定值

第三层对设备id mid_id 和 差值 diff做一个分组，且保留同组计数大于3的分组（这时的细粒度是由mid_id和diff共同构成）

第四层我们最后需要得到的是mid_id,所以对mid_id再做一次去重，去掉midid相同但是时间不同的分组，得到唯一的mid_id

因为比较复杂这里给出图解

46cb31189ddef88b25ed4182fc756420.png 
insert into table ads_continuity_uv_count
select
    '2020-04-29',
    concat(date_add('2020-04-29',-6),'_','2020-04-29'),
    count(*)
from
    (
    select 
        mid_id
    from
        (
        select 
            mid_id
        from
            (
            select
                mid_id,
                date_sub(dt,rank) date_dif
            from
                (
                select
                    mid_id,
                    dt,
                    rank() over(partition by mid_id order by dt) rank
                from 
                    dws_uv_detail_daycount
                where dt>=date_add('2020-04-29',-6) and
                    dt<='2020-04-29'
                )t1
            )t2
        group by 
            mid_id,date_dif
        having count(*)>=3
        )t3
    group by 
        mid_id
    )t4;
weixin_39680121
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQL学习笔记之汇总数据（聚集函数）
weixin_39729469的博客
03-21 270
聚集函数 对表中某些行运行的函数，计算并返回一个值 AVG()函数 对表中的列计算平均值，可以计算特定行的平均值，也可以计算多个列算数结果的平均值，但是不同列的平均值必须单独计算 select avg(prod_price) as avg_price from Products; 返回所有商品价格的平均值 select avg(prod_price) as avg_price from Pro...
Oracle 获取上周一到周末日期的查询sql语句
12-16
代码如下: — Oracle 取上周一到周末的sql — 这样取的是 在一周内第几天，是以周日为开始的 select to_char(to_date(‘20130906′,’yyyymmdd’),’d’) from dual; –结果：6 注释：2013.09.06是周五，为本周的第六天 select to_char(sysdate+(2-to_char(sysdate,’d’))-7,’yyyymmdd’) from dual;—上周一 select to_char(sysdate+(2-to_char(sysdate,’d’))-1,’yyyymmdd’) from dual;—上周日 —
oracle Sql学习笔记
song12345xiao的博客
12-08 233
oracle Sql学习笔记
ORACLE时间处理大全
cubu8303的博客
12-14 199
很实用的时间处理知识点。举例说明：1、日期字符转换函数to_date(),to_char() SQL：select to_date('20080229132545','yyyy-mm-dd hh24:mi:ss') from...
MySQL - 那些遇到的坑儿 - 收集
william_n的博客
04-21 4048
收集记录曾经遇到的坑儿，学习思考，避免再遇到，或者很快解决。
SQL拿捏：活跃用户数统计案例类型SQL 全网拿捏同款SQL
m0_49303490的博客
05-03 611
活跃用户数统计
SQL-剔除周六日，节假日，非工作时间
热门推荐
happytjl的博客
08-27 1万+
需求是：求每个请求ID操作时间和验收时间之间的间隔，需要剔除周六日，节假日，非工作时间（我们的工作时间 09:00-17:30 午休时间12:00-13:00 午休时间不属于工作范围，需要剔除） 当看到这个需求的时候，就感觉让实现色彩斑斓的黑一样，整个人是没有思绪的 但是最后发现还是可以完成的 比如现有的数据： 这个表的字段意思：请求ID,操作时间，验收时间 实现这个功能需要两个表辅助： 第一个表：TB_IP_Times（该表就是存放每一天的日期） 第二个表：TB_IP_.
mysql商品查询_MYSQL——怎么一个sql语句查询出用户和用户商品的列表啊
weixin_32506743的博客
02-02 1907
我现在都是先写一个sql查出所有用户数组，然后foreach循环这个数组拿id查他的所有商品数据，有没好点儿的方法商品的基本数据是可以缓存的 尤其是大访问量很明显数据库走的越少越好但是用户买了什么商品还是要走数据库 好一点就是读写分离咯如果用户id有外键就用left join查一下。如果没有索引的话就你这个方法就行了。join语句可以实现这个同时取出来就得join了，适当加索引，封装成存储过程来调...
SQL Server 中统计各用户表记录条数 的两种方法
weixin_34363171的博客
10-27 548
最近做数据监控遇到这么个查询需求，就从系统存储过程[sys].[sp_tables]中征用了遍历用户表的代码，组织一下，配合以MSSQL 中的表变量，写了如下代码： 方法一：     DECLARE @NAME VARCHAR(50)     DECLARE @SQL VARCHAR(1000)          SET @SQL = '             DECLARE...
数据采集系统_按日期为表名称一日自动更新一个表_组态王KingView6.55_数据库SQLServer2014.zip
12-12
数据采集系统，采用小巧好用的组态王KingView6.55和数据库SQLserver，搭建数据采集系统，在建模之前需要采集数据，采集数据的格式是每天生成一个表，表的名称为当前系统日期。搭建方法和代码手把手教授！傻瓜式教授...
SQL 判断给定日期值(或时间段)所在星期的星期一和星期天的日期
12-15
一、给定一个日期值，求出此日期所在星期的星期一和星期天的日期数据 例如给定一个日期 2010-09-01，求出它所在星期的星期一是2010-08-30，星期天是2010-09-05 Function创建如下: 代码如下: USE [MSSQL] GO SET ANSI...
sql server编写通用脚本实现获取一年前日期的方法
01-21
　在数据库编程开发中，有时需要获取一年前的日期，以便以此为时间的分界点，查询其前后对应的数据量。例如： 1. 想查询截止到一年前当天0点之前的数据量，以及一年前当天0点开始到现在的数据量。 2. 想查询截止到...
SQL 返回期间内的所有日期
12-15
代码如下:select rownum as dateIndex,to_date(‘20090701′,’yyyy-mm-dd’)+ rownum-1 AS DATE_ FROM DUAL CONNECT BY LEVEL >= 1 AND LEVEL <=(TO_DATE(‘20090731′,’yyyy-mm-dd’)-TO_DATE(‘20090701′,’...
mysql 条件位运算实现多值存储
最新发布
moneywenxue的博客
01-03 1029
mysql 条件位运算位运算实现多值存储，方法适合数据范围有限，且不会变更在业务上往往会出现多选的情况，例：选择 周一 至 周日 随意组合；数据在设计时就会如何去储存？七个二进制分别代表 周一至周日，0-未选 1-选中，例： 选择了周日、周一、 周二。对应二进制位：11000001，数据库储存十进制：67注意：mysql位运算，一个字段表示多选值；这种方式可以提高查询效率，减少like语句的应用;这种方式，。比如张三，第一次勾选了周一，第二次想新增周三、周四，那么前端传值：1,8,16；
SQL常用语句
您的欣博客
11-19 2208
一、删除篇 1.删除多个手机号 DELETE FROM 表名 where phone in (15231227,13395309,13634884); 2.删除单个手机号 DELETE FROM 表名 where phone = 189100562; 3.先查括号里的手机号对应user_id，再删除该表的user_id DELETE FROM 表名 WHERE user_id in (SELECT id from firm_sole_user where phone = 189100532); 二、查
基于组织的数据权限SQL查询方案
小单的博客专栏
03-21 1003
背景 一些软件系统需要根据组织做数据查看权限限制，比如可以设置张三能查看或管理1个或多个组织的数据。在对张三进行配置后，张三这个账号查询对应的业务数据表时需要带上数据权限有关的where条件。 一、主要表介绍 组织表的主要字段：ID、CODE、NAME、上级CODE、CODE全路径 sys_organization 数据权限表的主要字段：用户ID、组织ID、是否包含下级 sys_user_data_permission 测试的一个业务数据表中的主要字段：主键ID、业务数据字段、产生数据的组织ID、产生数
sql 取一个月日期去掉周末的所有日期_从零学会SQL--简单查询 第二关作业
weixin_39537397的博客
12-09 199
练习1:查询学生表在school数据库，点查询/右键-新建查询，输入SQL查询语句：（*是查询所有列）select *from student运行，得到如下查询结果：练习2:理解sql运行顺序sql运行顺序是：先顺次运行除select子句之外的sql语句，最后运行select语句。选取姓名列里值为猴子的行：注意：返回结果中列的顺序是由select子句中列名的次序决定的，比如，原表中“学号”是第一列...
AnalyticDB分析型数据库
qq_33439938的博客
09-13 4095
AnalyticDB分析型数据库 本文初衷是为了学习归纳，若有错误，请指出。 修改记录 时间 内容 2020年9月13日 第一次发布 一、概述 1.1 定义 ​ 分析型数据库AnalyticDB（原名 ADS）是阿里巴巴针对海量数据分析自主研发的实时高并发在线分析系统，可以针对万亿级别的数据进行多维度分析透视和业务探索。采用分布式计算，具有强大的实时计算能力。 1.2 特点 ​ 主要特点就是实时和高并发，可以针对万亿级别的数据进行多纬度分析透视和业务探索。 兼容MySQL、B
sql里面取上个月日期的函数有啥
06-07
在SQL中，可以使用以下函数来获取上个月的日期： 1. DATEADD函数 DATEADD函数可以在日期上添加或减去指定的时间间隔。通过将月份参数设置为负值，可以获取上个月的日期。例如，以下查询将返回上个月的当前日期： ``` SELECT DATEADD(MONTH, -1, GETDATE()) ``` 2. DATE_TRUNC函数 DATE_TRUNC函数可以截断日期，使其只保留指定的时间部分。通过将日期设置为当前月份的第一天，然后减去一天，可以获取上个月的最后一天。例如，以下查询将返回上个月的最后一天： ``` SELECT DATE_TRUNC('MONTH', GETDATE()) - INTERVAL '1' DAY ``` 3. DATE_FORMAT函数 DATE_FORMAT函数可以将日期格式化为指定的字符串。通过将日期设置为当前月份的第一天，然后减去一秒钟，可以获取上个月的最后一秒钟。例如，以下查询将返回上个月的最后一秒钟： ``` SELECT DATE_FORMAT(DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 1 SECOND), '%Y-%m-%d %H:%i:%s') ``` 这些函数的具体语法和用法可能会因SQL的不同而有所差异，上述只是一些常见的方法。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值