实习中增长的数据查询经验——SQL

最新推荐文章于 2024-06-13 09:00:30 发布

依凌的小精灵

最新推荐文章于 2024-06-13 09:00:30 发布

阅读量162

点赞数

分类专栏： SQL 文章标签：数据库

本文链接：https://blog.csdn.net/qq_36860570/article/details/119797717

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本篇文章主要是对入职以来两个多月的hive SQL查询进行总结，希望在不断实习和做数据需求中积累更多的经验。加油！！！

有个注意事项要提前说明一下，Hive SQL和SQL还是有些区别的，所以有些函数没往上写，部分函数说明是借鉴别的博客和官网说明，不是抄袭贴。
昂，我来二更了，我们用的smart出数据报表，其中报表有两种出数方式，一种是lighting（支持大量数据的逻辑计算，需要我们在我们的库中先处理数据再呈现在smart中），一种是MySQL（支持小量数据，这种是先建表，数据从他们的sql库中处理数据，小量数据的处理也不容易崩）

一、实际数据中的各种时间类型

1.datetime——标准时间数据类型

● datetime类型中其实包含几种不同的时间类型，例如date，time等等。
● datetime类型的数据可以直接利用datediff()或者dateadd()函数进行时间上的加减
● DATETIME类型用在你需要同时包含日期和时间信息的值时。MySQL检索并且以'YYYY-MM-DD HH:MM:SS'格式显示DATETIME值，支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

2.string——字符串时间数据类型

有时一些时间数据会被保存成字符串格式，方便进行不同的数据转化
DATE只保存日期，不保存时分秒

3.timestamp——时间戳数据类型

TIMESTAMP列类型提供一种类型，你可以使用它自动地用当前的日期和时间标记INSERT或UPDATE的操作。如果你有多个TIMESTAMP列，只有第一个自动更新。

自动更新第一个TIMESTAMP列在下列任何条件下发生：

列没有明确地在一个INSERT或LOAD DATA INFILE语句中指定。

列没有明确地在一个UPDATE语句中指定且一些另外的列改变值。（注意一个UPDATE设置一个列为它已经有的值，这将不引起TIMESTAMP列被更新，因为如果你设置一个列为它当前的值，MySQL为了效率而忽略更改。）

你明确地设定TIMESTAMP列为NULL.

除第一个以外的TIMESTAMP列也可以设置到当前的日期和时间，只要将列设为NULL，或NOW()。

自动更新TIMESTAMP时建表语句应为如下类似
CREATE TABLE `t_jindou_xx` (
  `account` varchar(18) NOT NULL default '',
  `money` decimal(10,2) NOT NULL default '0.00',
  `update_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`account`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

TIMESTAMP类型保存的值不能比1970早或比2037晚，这意味着，一个日期例如'1968-01-01'，当作为一个DATETIME或DATE值合法时，它不是一个正确TIMESTAMP值，并且 如果赋值给这样一个对象，它将被变换到0。

4.timestamp和datetime比较

datetime

1、允许为空值，可以自定义值，系统不会自动修改其值。

2、不可以设定默认值，所以在不允许为空值的情况下，必须手动指定datetime字段的值才可以成功插入数据。

3、虽然不可以设定默认值，但是可以在指定datetime字段的值的时候使用now()变量来自动插入系统的当前时间。

结论：datetime类型适合用来记录数据的原始的创建时间，因为无论你怎么更改记录中其他字段的值，datetime字段的值都不会改变，除非你手动更改它。

 

timestamp

1、允许为空值，但是不可以自定义值，所以为空值时没有任何意义。

2、默认值为CURRENT_TIMESTAMP()，其实也就是当前的系统时间。

3、数据库会自动修改其值，所以在插入记录时不需要指定timestamp字段的名称和timestamp字段的值，你只需要在设计表的时候添加一个timestamp字段即可，插入后该字段的值会自动变为当前系统时间。

4、以后任何时间修改表中的记录时，对应记录的timestamp值会自动被更新为当前的系统时间。

结论：timestamp类型适合用来记录数据的最后修改时间，因为只要你更改了记录中其他字段的值，timestamp字段的值都会被自动更新。

二、各种时间类型的处理函数(部分为字符串处理函数)

目前实习遇到过很多的时间数据，在时间数据中有不同的类型，一般的时间数据最终都要输出成datetime类型的，方便进行各种时间（例如datediff()）的处理

1.to_char(data,‘format’)

to_char(data,'format') 函数是将数据变成想要的数据格式
例如：service_time为datetime类型，2014-06-04 16:30:00为其中的一个数据

select to_char(service_time,'yyyy-mm-dd')//输出为2014-06-04
select to_char(service_time, '阿里金融yyyyddmm')//输出为阿里金融20140604

注意，此时的输出是字符串格式，要想用datediff(time1,time2,‘dd’)函数，其中的time1和time2必须是datetime类型

2.to_date(datetime, ‘format’)

用途： 将一个字符串按照format指定的格式转成日期值。 
参数说明： 
●  datetime: string类型，要转换的字符串格式的日期值，若输入为bigint, double, datetime类型会隐式转换为string类型后参与运算，为其它类型抛异常，为空串时抛异常。
●  format: string类型常量，日期格式。非常量或其他类型会引发异常。format参数中标识的间类型的元素（如年、月、日等），可选值见上表10-1，其他字符作为无用字符在parse时忽略。format参数至少包含’yyyy’，否则引发异常，如果format string中出现多余的格式串，则只取第一个格式串对应的日期数值，其余的会被视为分隔符。
如to_date('1234-2234 ', 'yyyy-yyyy ')会返回1234-01-01 00:00:00
返回值： 
    返回datetime类型。若任一输入为NULL，返回NULL值。

select to_date(time ,'yyyy-mm-dd')//其中time为字符串类型，最终返回的是时间类型

3.cast(expr as)

用途： 
    将表达式的结果转换成目标类型，如cast(‘1’ as BIGINT)将字符串’1’转为整数类型的1，如果转换不成功或不支持的类型转换会引发异常。
参数说明：
●  cast(double as bigint)，将double值trunc成bigint
●  cast(string as bigint) 在将字符串转为BIGINT时，如果字符串中是以整型表达的数字，会直接转为BIGINT类型。如果字符串中是以浮点数或指数形式表达的数字，则会先转为DOUBLE类型，再转为BIGINT类型。
●  cast(string as datetime) 或 cast(datetime as string)时，会采用默认的日期格式yyyy-mm-dd hh:mi:ss。

下面的bind_time的数据类型是timestamp
其中的一个数据为：2019-03-18 11:42:18.000

select cast(bind_time as datetime)//输出为2019-03-18 11:42:18

4.substr(string1, start_position[, length])

用途： 
    返回字符串string1从start_position开始长度为length的子串。
参数说明： 
●  string1: string类型，若输入为bigint, double, datetime类型会隐式转换为string后参与运算，其它类型报异常。
●  start_position: bigint类型，当start_position为负数时表示开始位置是从字符串的结尾往前倒数，最后一个字符是-1。其它类型抛异常。
●  length: bigint类型，大于0，其它类型或小于等于0抛异常。子串的长度。 
返回值:
    在string1中的子串，若任一输入为NULL,返回NULL.
备注： 
    当length被省略时，返回到string1结尾的子串。

示例：
● substr("abc", 2) 返回bc
● substr("abc",1,2)返回b
这个函数在时间间隔计算中很好用，可以取到每个时间的月或年再加减’

select        bind_time,        SUBSTR(bind_time,7 ),   SUBSTR(bind_time,1,7 )
//输出为：2019-03-18 11:42:18.000  3-18 11:42:18             2019-03

5.concat(string A, string B…)

参数说明：
●  A,B等为string类型，若输入为bigint, double, datetime类型会隐式转换为string后参与运算，其它类型报异常。
返回值： 
    string 
用途： 
    返回值是将参数中的所有字符串连接在一起的结果。 
备注：
    如果没有参数或者某个参数为NULL，结果均返回NULL
concat(), concat(null, 'a'), concat('a', null, 'b')返回值都是NULL。

select concat('a', 'null', 'b')//输出为：anullb

三、不同的取数方法

1.统计x天以上首次下单的用户数据

首先解释一下，x天以上首次下单的含义是在最近的x天内数据表中没有记录但在现在有记录的用户，不用考虑在x天以前是否有记录。

例如：想要取时间段为：7月1号到7月31号，之前30天未下单的首次下单的用户数，要弄清楚这个30天是静态的30 还是动态的30天。

静态的30天 ：无论统计的是7月1号的数据还是7月31号的数据，标签都是7月1号以前30天未下单的用户
动态的30天：7月1号统计的是前30天也就是6月1号到6月30号未下单的用户，7月2号统计的是6月2号到7月1号未下单的用户数据

静态30天的取数逻辑：
取用户的订单表和前三十天的订单数据进行 left join ，对前三十天数据为空的用户打上未下单标签，按照日期分组，
count( distinct case when 用户标签为未下单 then user_id else null end )

动态30天的取数逻辑：
取想要的时间段内的用户订单数据（包含time1），和用户订单的整体数据（包含time2）进行关联，关联时设置条件time1>time2，这样可以筛选出不在当前时间之后的用户订单信息，这时取的max(time2) as max_time就是在我们统计的时间之前的最近下单时间，如果这个时间和time1的时间差大于30就认为是30天以上首次下单的用户，这时还可以顺便统计新用户的个数max_time is null，以下是一个比较经典的例子：

select time1,
	count(distinct case when tag_time=1 then user_id else null end ) 30天以上首次下单用户数,
    count (distinct case when max_time is null then user_id else null end ) 新用户数
 from(
     SELECT user_id,time1,max_time,
         case when DATEDIFF(to_date(time1 ,'yyyy-mm-dd') ,to_date(max_time ,'yyyy-mm-dd'),'dd')>30 then 1 else 0 end tag_time
     from(
         SELECT c1.user_id,time1,max(time2) max_time
         FROM 
         (
             select user_id,to_char(service_time,'yyyy-mm-dd') time1
             from table1
             where to_char(service_time,'yyyy-mm-dd') between '2021-07-01' and '2021-07-30' 
         )c1
         left join
         (
             select user_id,to_char(service_time,'yyyy-mm-dd') time2
             from table1
         ) c2  on c1.user_id= c2.user_id  and time1>time2
         group by c1.user_id,time1
     )
 )
 group by time1

2.在取数结果中显示数据的类别(group by 中使用case when )

现实情境：订单等候时长（wait_time）分布（40以下，40-60，60以上的数量），wait_time is null 为0，(0,40]为1，(40,60]为2，>60为3，以下为部分结果：

城市	类别	时长个数
北京	0	50
北京	1	100
北京	2	47
北京	3	68

select city_name
       ,case when wait_time is null then 0 
             when wait_time between 0 and 39  then 1 
             when wait_time between 40 and 59 then 2
             when wait_time between 60 and 120 then 3
             else 4 end as wait_time_lev
       ,count(a.order_id) as order_cnt
from table2 a
left join ( select order_id
            from table2 ) b on a.order_id = b.order_id
where city_name ='北京'
group by city_name
        ,case when wait_time is null then 0 
             when wait_time between 0 and 39  then 1 
             when wait_time between 40 and 59 then 2
             when wait_time between 60 and 120 then 3
             else 4 end;

3.次日留存，三日留存，七日留存用户问题

这个问题是在实习中遇到的最多的问题，而且每次都要纠结好久，不知道怎么给用户打标签[裂开.jpg]
不管次日，3日还是7日，最终都是在看用户后续会不会下单。
解释一下留存的定义：
7日留存指的是在7天内再次下单
3日留存是3日内再次下单，次日就不解释了

取数逻辑：取两张订单表，一张是近期的用户下单情况，一张是x日留存的时间段数据，进行关联；取出两个表中的time1和time2（都是datetime类型的字段），利用datediff(time2,time1,'dd')<=x来判断是否x日留存

下面是一个7日留存的具体的例子：

select m.service_date1
     ,count(distinct case when datediff(cast(service_date2 as datetime),cast(service_date1 as datetime),'dd')<=7 then m.user_id else null end) Y 
from 
	(
        SELECT service_date1,a.user_id
        FROM 
        (
	        SELECT to_char(service_time,'yyyy-mm-dd') as service_date1 ,user_id
	        from  table1
	        Where to_char(service_time,'yyyy-mm-dd') between '2021-07-05' and '2021-07-14'
	        group by to_char(service_time,'yyyy-mm-dd'),user_id 
        ) a
        group by service_date1,a.user_id
    )m  
left join  
    (
        SELECT to_char(service_time,'yyyy-mm-dd') service_date2,user_id
        from  table1
        Where to_char(service_time,'yyyy-mm-dd') between '2021-07-12' and '2021-07-14'
        group by to_char(service_time,'yyyy-mm-dd'),user_id 
    ) n
on m.user_id = n.user_id 
group by m.service_date1;

4.月留存，月持续留存

其中的${time}是我们在取数时可以手动输入的日期

max( s_time )  as last_s_time
max( case when substr(s_time,1,7) = 
			   substr(dateadd( to_date('${time}','yyyy-mm-dd'),-1,'mm' ),1,7) 
		  then s_time else null end )  as  上个月是否有记录
max( case when substr(s_time,1,7) <= 
			   substr(dateadd( to_date('${time}','yyyy-mm-dd'),-2,'mm' ),1,7) 
		  then s_time else null end )  as  上上月是否有记录

5.月峰值数据量（排名问题）

这种取数需求一般是取每一组排名前几的数据，涉及到的函数为
row_number() over(partition by 分组列 order by 排序列 desc)
在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行
例如，我们想看每个城市的历史峰值月及其峰值量

select * 
from 
(
    select ROW_NUMBER()over(partition by 城市  order by order_cnt desc) rowId,*
    from 
    (
        SELECT a.city_name 城市,to_char(service_time,'yyyy-mm-dd') as 日期 ,
          count(order_id) as  月单量
        from  table1 a
        group by a.city_name,to_char(service_time,'yyyy-mm-dd') 
    )
) cnt_Records
where rowId=1;//想取前X个数只需要rowID<=X即可

最终输出大致如下：

rowid	城市	日期	月单量
1	北京	2014-01-03	200000
1	上海	2021-01-01	25000
1	杭州	2020-06-09	8000
1	南京	2014-03-01	5000

6.行转列问题

假设原表为：

日期	城市	订单类型	订单量	订单总金额	退单量
2021-08-09	北京	服装类	30000	480000	2500
2021-08-09	北京	零食类	31000	500000	2300
2021-08-09	北京	家纺类	2500	9000	100
2021-08-09	上海	服装类	4000	60000	900
2021-08-09	上海	零食类	40000	900000	2900
2021-08-09	上海	家纺类	8000	10000	3000
2021-08-09	南京	零食类	23000	450000	2100
2021-08-09	南京	家纺类	3000	20000	300

将上表行转列，最终要达到的效果如下：

日期	城市	统计维度	服装类	零食类	家纺类
2021-08-09	北京	订单量	30000	31000	2500
2021-08-09	北京	订单总金额	480000	500000	9000
2021-08-09	北京	退单量	2500	2300	100
2021-08-09	上海	订单量	4000	40000	8000
2021-08-09	上海	订单总金额	60000	900000	10000
2021-08-09	上海	退单量	900	2900	3000
2021-08-09	南京	订单量	-	23000	3000
2021-08-09	南京	订单总金额	-	450000	20000
2021-08-09	南京	退单量	-	2100	300

select '${d_date}' as stat_date,city_name, '订单量' as tag,
        nvl(sum( case when a.user_tag='服装类' then cnt_d else null end ),0) 服装类,
        sum( case when a.user_tag='零食类' then cnt_d else null end ) 零食类,
        sum( case when a.user_tag='家纺类' then cnt_d else null end ) 家纺类
from table1
where dt= '${d_date}'  
group by city_name
union ALL 
select '${d_date}' as stat_date,city_name, '订单总金额' as tag,
        nvl(sum( case when a.user_tag='服装类' then cnt_e else null end ),0) 服装类,
        sum( case when a.user_tag='零食类' then cnt_e else null end ) 零食类,
        sum( case when a.user_tag='家纺类' then cnt_e else null end ) 家纺类
from table1
where dt= '${d_date}'  
group by city_name
union ALL 
select '${d_date}' as stat_date,city_name, '退单量' as tag,
        nvl(sum( case when a.user_tag='服装类' then cnt_t else null end ),0) 服装类,
        sum( case when a.user_tag='零食类' then cnt_t else null end ) 零食类,
        sum( case when a.user_tag='家纺类' then cnt_t else null end ) 家纺类
from table1
where dt= '${d_date}'  
group by city_name

依凌的小精灵

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
实习中增长的数据查询经验——SQL

本篇文章主要是对入职以来近两个月的SQL查询进行总结，希望在不断实习和做数据需求中积累更多的经验。加油！！！SQL一、统计x天以上首次下单的用户数据（统计时间为七月整月）二级目录三级目录bind_timetimestamp用户绑定时间一、统计x天以上首次下单的用户数据（统计时间为七月整月）首先解释一下，x天以上首次下单的含义是在最近的x天内数据表中没有记录但在现在有记录的用户，不用考虑在x天以前是否有记录二级目录三级目录...
复制链接

扫一扫