测试工作 常用的SQL函数（持续更新）

仅记录自己在工作中常见的SQL函数（不同的数据源类型使用的函数可能不一样）

• CAST：字段的类型转换

-- 转换字段类型
SELECT ship_mode,
  CAST(postal_code AS int) AS postal_code
FROM dp_beacon_sample_demo_uuid
group BY ship_mode,
  postal_code
ORDER BY postal_code DESC
LIMIT 5000;

• case when：用于根据一个或多个条件返回不同的结果

-- case when
select category,
  case
    when ship_mode = 'Same Day' then 'Same Day翻译'
    when ship_mode = 'Second Class' then 'Second Class翻译'
  end as ship_mode
from dp_beacon_sample_demo_uuid
group by category
limit 1000;

• with？？？ 还没学会
• 时间转换相关：date_trunc、date_format、from_unixtime、unix_timestamp

-- date_trunc 是一个用于截断日期和时间值的 SQL 函数。 mysql不支持，presto的函数
-- date_format 是一个用于格式化日期和时间值的 SQL 函数
-- from_unixtime 是一个用于将 Unix 时间戳（以秒为单位）转换为日期和时间值的 SQL 函数
-- unix_timestamp 是一个用于获取 Unix 时间戳（以秒为单位）的 SQL 函数

select date_format(date_trunc('week',from_unixtime(unix_timestamp(order_date, 'yyyy-MM-dd HH:mm:ss'))),'%Y%m%d') as order_date_monday,
  count(distinct id) as total_id
from tdw_day_pep_order_demo_qimei36
where imp_date = '20240223'
group by date_format(date_trunc('week',from_unixtime(unix_timestamp(order_date, 'yyyy-MM-dd HH:mm:ss'))),'%Y%m%d')
order by date_format(date_trunc('week',from_unixtime(unix_timestamp(order_date, 'yyyy-MM-dd HH:mm:ss'))),'%Y%m%d') desc

• concat：用于将两个或多个字符串连接在一起

-- concat 用于将两个或多个字符串连接在一起
select concat(country, '&', city) as country_city,
  concat('https://www.csdn.net/?spm=','1010.2135.3001.4476&ydreferer=aHR0cHM6Ly9pLmNzZG4ubmV0Lw%3D%3D') as url
  from tdw_day_pep_order_demo_qimei36
where imp_date = '20240223'

•  locate：返回目标字符串在源字符串中首次出现的位置
•  length：用于返回一个字符串的长度（以字节为单位）

-- locate 返回目标字符串在源字符串中首次出现的位置
-- length 用于返回一个字符串的长度（以字节为单位）
SELECT ship_mode,
  locate('Class',ship_mode),
  postal_code,
  length(postal_code)
FROM dp_beacon_sample_demo_uuid 
WHERE length(postal_code) > 0 AND locate('Class',ship_mode) > 0
LIMIT 5000;

• SUBSTRING_INDEX：将一个字符串按照指定的分隔符拆分为一个数组，并返回指定位置的元素

-- SUBSTRING_INDEX 将一个字符串按照指定的分隔符拆分为一个数组，并返回指定位置的元素
-- SUBSTRING_INDEX(string, delimiter, count)，count 是一个整数，表示要返回的元素的位置。如果 count 为正数，则从左到右计算；如果 count 为负数，则从右到左计算
select category,
  SUBSTRING_INDEX(ship_mode,' ',-1)
from dp_beacon_sample_demo_uuid
group by category
limit 1000;

• IF：函数用于根据条件返回两个值中的一个

-- 函数用于根据条件返回两个值中的一个
-- IF(condition, value_if_true, value_if_false)

select category,
  if(length(postal_code)>0,'postal_code有值','postal_code为空')
from dp_beacon_sample_demo_uuid
limit 1000;