mysql函数及关键字使用

Dancer-

已于 2022-08-22 18:17:31 修改

阅读量2.1k

点赞数

文章标签： mysql 数据库 java

于 2022-08-10 10:52:58 首次发布

本文链接：https://blog.csdn.net/weixin_72993139/article/details/126262149

版权

①collect_set() collect_set (col)函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

②MySQL中concat函数（连接字符串）

MySQL中concat函数

使用方法：
concat(str1,str2,…)

返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

mysql> select concat('11','22','33');
+------------------------+
| concat('11','22','33') |
+------------------------+
| 112233 |
+------------------------+
1 row in set (0.00 sec)

MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL
mysql> select concat('11','22',null);
+------------------------+
| concat('11','22',null) |
+------------------------+
| NULL |
+------------------------+
1 row in set (0.00 sec)

③关键字LIKE可以进行模糊匹配

④

SQL INNER JOIN 关键字

在表中存在至少一个匹配时，INNER JOIN 关键字返回行。

⑤

insert overwrite ... :可以保证计算任务的幂等性（即不管执行几次，都能保证和执行一次的结果是一样的，保证任务可以重复执行）

⑥字段拼接，满n件打折等优惠实现

select
    id,
    coupon_name,
    coupon_type,
    type_dic.dic_name,
    condition_amount,
    condition_num,
    activity_id,
    benefit_amount,
    benefit_discount,
    case coupon_type
        when '3201' then concat('满',condition_amount,'元减',benefit_amount,'元')
        when '3202' then concat('满',condition_num,'件打',10*(1-benefit_discount),'折')
        when '3203' then concat('减',benefit_amount,'元')
    end benefit_rule,
    create_time,
    range_type,
    range_dic.dic_name,
    limit_num,
    taken_count,
    start_time,
    end_time,
    operate_time,
    expire_time
from

⑦orc：ORC文件是以二进制方式存储的，所以是不可以直接读取

tsv：以逗号分隔的文件，本质为文本文件

⑧表是否是分区表，对于之后load文件上去的区别

DROP TABLE IF EXISTS tmp_dim_date_info;
CREATE EXTERNAL TABLE tmp_dim_date_info (
    `date_id` STRING COMMENT '日',
    `week_id` STRING COMMENT '周ID',
    `week_day` STRING COMMENT '周几',
    `day` STRING COMMENT '每月的第几天',
    `month` STRING COMMENT '第几月',
    `quarter` STRING COMMENT '第几季度',
    `year` STRING COMMENT '年',
    `is_workday` STRING COMMENT '是否是工作日',
    `holiday_id` STRING COMMENT '节假日'
) COMMENT '时间维度表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/tmp/tmp_dim_date_info/';

若表不是分区表，在先将表建出来之后再将文件load到此路径下的情况下，这个表能识别此文件数据

若表已分区，在先将表建出来之后再将文件load到此分区路径下的情况下，这个表不能识别此文件数据。要想能识别，我们要执行一下msck repair table修复元数据信息

⑨内外部表

DROP TABLE IF EXISTS dim_date;
CREATE EXTERNAL TABLE dim_date
(
    `date_id`    STRING COMMENT '日期ID',
    `week_id`    STRING COMMENT '周ID,一年中的第几周',
    `week_day`   STRING COMMENT '周几',
    `day`        STRING COMMENT '每月的第几天',
    `month`      STRING COMMENT '一年中的第几月',
    `quarter`    STRING COMMENT '一年中的第几季度',
    `year`       STRING COMMENT '年份',
    `is_workday` STRING COMMENT '是否是工作日',
    `holiday_id` STRING COMMENT '节假日'
) COMMENT '时间维度表'
    STORED AS ORC
    LOCATION '/warehouse/gmall/dim/dim_date/'
    TBLPROPERTIES ('orc.compress' = 'snappy');
若建表为分区表并且是外部表，删除后再重建，数据不会丢。文件依然存在，但是查不出来：因为你删表之后把这张表的元数据信息也删除了，之后你再重建，分区对不上。要想查出来：msck repair table一下就行

10.数据脱敏方式

使用加密算法。如常用的md5（）

11.data_sub()

date_sub('2020-06-15',1) 获取2020-06-14

12.union关键字

连接两个select （注意不能union两个表，只能union两个select）

其中union all 是不去重

union会去重

两个select union后就把他看成一个select查询去处理就行了

（要使用union 必须保证上下两个select查询的字段个数一致，类型一致）

13.cast（）函数可以实现类型转换

将data转成string方式
date_sub('2020-06-15',1)

cast(date_sub('2020-06-15',1) as string)

14.like模糊匹配通配符

使用在sql语句中where过滤时通配符为 %

使用在ddl语言中通配符为*

15.严格模式与非严格模式

set hive.exec.dynamic.partition.mode=nonstrict; 非严格模式

set hive.exec.dynamic.partition.mode=strict; 严格模式（当你使用动态分区时，要想使用严格模式，必须保证动态分区字段里至少有一个字段是静态的）

16.将日期类型格式化

将日期字段date_id 转化为yyyy-MM-dd样式
date_format(date_id,'yyyy-MM-dd') date_id

（注：在mysql中为%y-%M-%d 在hive中为yyyy-MM-dd）

17.map_keys() array_contains()

 array_contains(map_keys(old),'payment_status')

18.开窗函数

Windowing functions
- LEAD（取窗口内某一列下n行的值）
  - The number of rows to lead can optionally be specified. If the number of rows to lead is not specified, the lead is one row.
  - Returns null when the lead for the current row extends beyond the end of the window.
- LAG（取窗口内某一列上n行的值）
  - The number of rows to lag can optionally be specified. If the number of rows to lag is not specified, the lag is one row.
  - Returns null when the lag for the current row extends before the beginning of the window.
- FIRST_VALUE（可以实现的功能为：获取窗口内某一列的第一个值）
  - This takes at most two parameters. The first parameter is the column for which you want the first value, the second (optional) parameter must be a boolean which is false by default. If set to true it skips null values.
- LAST_VALUE（可以实现的功能为：获取窗口内某一列的最后一个值）
  - This takes at most two parameters. The first parameter is the column for which you want the last value, the second (optional) parameter must be a boolean which is false by default. If set to true it skips null values

例子：

mid page ts          first_value(page) over(partition by mid order by ts)
1 null 1 null
1 a 2 null
1 b 3 null
1 c 4 null
1 null 5 null
                first_value(page,true) last_value(page,true)
                null null
                  a a
                a b
                a c
                a c

mid page ts          sum_value(page) over(partition by mid order by ts)
1 1 1                 1
1 1 2                 2
1 1 3                 3
1 1 4                 4
1 1 5                 5
                sum_value(page) over(partition by mid)
                5
                5
                5
                5
                5

19.窗口函数的窗口范围

over(partition by mid order by ts)
若没写明窗口范围(即未自定义窗口范围)（即没写rows ... betweent ....and ....）
窗口范围取默认值
分两种情况：
一：有order by->窗口范围为分区内第一行到当前行
二：没有order by->窗口范围为分区内第一行到最后一行

20.日期格式化涉及时区时要注意的点

date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,

若ts为秒，且为整数，我们要乘以1000，或者把整数转成小数也行

若ts为毫秒，则不用

21.UDTF回顾

tbl
id arr
1001 [a,b,c]
1002 [c,d]

select
*
from tbl lateral view explode(arr) tmp as item
此时返回的结果为一张虚表，为5行3列
id arr item
1001 [a,b,c] a
1001 [a,b,c] b
1001 [a,b,c] c
1002 [c,d] c
1002 [c,d] d
即当执行的语法为lateral view后，此时查询的表以及不是tbl了，而是你搭建的一个虚表(tbl lateral view explode(arr) tmp as item)
其中item为列名，tmp为你炸裂炸出来的这个表的表名（炸出来的所有字段组成的表，不包含原来的表，当explode(map)时可以较为明显的看出（from tbl lateral view explode(map) tmp as key,value）,即tmp为key和value组成的表）。设计这个tmp的作用：当炸出来的列被命名和原表相同的名字时（如id），可以用tmp.id和原表区分开

注：explode()函数只能接受hive中的数组类型或者map

22.字段中含有null时 count(1) count(*) 结果的区别

count(distinct (if(order_count_7d>0,user_id,null)))
对于count来说，null是不会被统计在内的
举例：若字段a一共有5行，分别为 null，1，2，3，4
count（a）结果为4
count（1）结果为5 一共有几行就统计几行
假设这个表里还有其他字段 count（*）结果为 5

23.nvl（）函数

nvl(new.user_id,old.user_id)        第一个参数不为空则返回第一个参数，为空则返回第二个参数

24.union 两个select，会生成一个虚表，这个虚表的字段名以第一个select为准

25.解决表不分区，装载时要求数据不重复，且不能有小文件问题

若使用insert into 则每次insert都是一个或若干个新文件，会产生小文件问题

insert overwrite table ads_traffic_stats_by_channel
select * from ads_traffic_stats_by_channel
union
select
    '2020-06-14' dt,
    recent_days,
    channel,
    cast(count(distinct(mid_id)) as bigint) uv_count,
    cast(avg(during_time_1d)/1000 as bigint) avg_duration_sec,
    cast(avg(page_count_1d) as bigint) avg_page_count,
    cast(count(*) as bigint) sv_count,
    cast(sum(if(page_count_1d=1,1,0))/count(*) as decimal(16,2)) bounce_rate
from dws_traffic_session_page_view_1d lateral view explode(array(1,7,30)) tmp as recent_days
where dt>=date_add('2020-06-14',-recent_days+1)
group by recent_days,channel;

其中overwrite每次会将原先hdfs路劲下的文件清空，然后再写入新文件，onion可以实现去重

（注：此处也应注意字段的数据类型是否会影响去重效果

例如：若建表时为DECIMAL数据类型，而此处装载时为count(*) as bigint sv_count

即此处为double类型，两个不同的数据类型hive不会帮我们去重，

所以我们在利用union进行去重时一定要注意字段类型是否是一致的，字段类型不一致也有可能会导致去重失效，故而不一致时可以强转等方式解决

）

26.rank()排序函数

rank() over (partition by session_id order by view_time) rk

27.inner join（即join）返回两部分相同的数据

28，datediff()

where datediff(t1.login_date_last,t2.login_date_last)>=8

传入两个日期类型，返回两日期间相隔的天数

29.使用full outer join 关联多表时注意事项

在关联条件上应该注意：如t1 full outer join t2 后，再和t3 full outer join 时 on 那里应该

写成nvl（t1.user_id，t2.user_id）=t3.user_id

由于nvl只能接收两个参数，当还有更多表要进行full outer join 时，可以嵌套

也可以使用coalesce（），此函数可以看作nvl的增强版，可以传多个参数