数据库常用函数

sql server：

int 相除保留2位有效数字：Round(1.0*1/3,2) =0.330000 ， 后面的0会被保留下来

如果用 Convert(decimal(6,2),1.0*1/3) 就可以实现保留2位数字 0.33 ，出来的类型是 decimal

CAST(1.0*1/3 as float) 可以把类型转为float型 / clickhouse CAST(1.0*1/3 as Float32) /

toFloat64(sum(DataVolume))

select convert(varchar(7), '2022-03-01',120) 截取 年月

clickhouse 常用函数

ifNull(x,alt) , 如果位null 就赋值alt

nullIf(x, y) ， 如果 x=y 则返回null ，不等就返回x

1. MySQL         Clickhouse

1. Decimal(9,2)  Decimal32(2)
2. Decimal(22,6) Decimal128(6)

CREATE TABLE datawarehouse.test_department
(
    `department` String,
    `name` Nullable(String),
    `cost` Nullable(Int32)
)
ENGINE = MergeTree
ORDER BY department
SETTINGS index_granularity = 8192

select *, row_number() over (partition by department order by cost desc) as row_number_result, --排序相同时不重复
rank() over(partition by department order by cost desc) as rank_result, --排序相同时重复 跳数字
dense_rank() over(partition by department order by cost desc) as dense_rank_result -排序相同时重复 不跳数字
from  datawarehouse.test_department

select * ,first_value(name) over (partition by department order by cost desc) as row_number_result, 
first_value(name) over(partition by department order by cost ) as rank_result
from  datawarehouse.test_department