工作常用sql 总结-长期更新

count(distinct  字段) over(partition by **) 

注意：spark 是不支持以上写法的

可以用两种方式实现

第一种：用size + collect_set 结合的方式代替，比如size(collect_set(t1.poi_id) over(partition by t.dt,t2.second_district_id)) as dis_cnt。但第一种有个数据量级比较大情况下，会计算不出来，需要注意

第二种：

通过两层的开窗函数，其中第一层 用row_number() over(partition by t.dt,order_num, t.shop_id)  as rn进行相关商家id的去重。第二层(在第一层外层)，用sum 的开窗函数sum(if(rn=1,1,0)) over(partition by t.dt,t.order_num) as rn_cnt

GROUP_CONCAT ：

GROUP_CONCAT函数用于将多行数据按照指定的分隔符连接成一个字符串，并返回结果。它通常与GROUP BY子句一起使用，用于对分组后的数据进行字符串拼接操作。

语法：GROUP_CONCAT([DISTINCT] expr [, expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [, col_name ...]] [SEPARATOR str_val])

参数说明：

• DISTINCT：可选参数，用于去重，表示只将不重复的值进行拼接。
• expr：要拼接的列或表达式。
• ORDER BY：可选参数，用于指定拼接结果的排序方式。
• ASC或DESC：可选参数，用于指定排序的升序或降序。
• SEPARATOR：可选参数，用于指定拼接结果的分隔符，默认为逗号。

下面是一个示例，演示如何使用GROUP_CONCAT函数：

例子

SELECT category, GROUP_CONCAT(product_name) AS products FROM products GROUP BY category;

mysql 支持，spark等可以用concat_ws实现比如替换例子：

select 
age,
concat_ws('&',collect_list(name)) as name
from 
test 
GROUP BY age

2、months_between 计算两个月份的差值。

select
months_between('2023-10-01','2023-01-01') 

3、SUBSTRING_INDEX

函数是一个字符串函数，用于从一个字符串中获取指定分隔符之前或之后的子字符串。

SUBSTRING_INDEX函数的语法如下：

参数说明：

• str：要进行处理的字符串。
• delimiter：分隔符，用于指定字符串的分隔规则。
• count：指定要获取的子字符串的个数。如果count为正数，则返回从字符串开头到第count个分隔符之间的子字符串；如果count为负数，则返回从字符串末尾到第count个分隔符之间的子字符串。

SUBSTRING_INDEX(str, delimiter, count)