clickhouse

SELECT Account,toInt64（arrayStringConcat(extractAll(Account, ‘[0-9]’),’’)） from finance_new_result.profit_result AM-367-UK=> 367
clickhouse正则表达式提取字符串数字
select Account, alphaTokens(Account),splitByChar(’-’,Account),arrayElement(splitByChar(’-’,Account),1) a,arrayElement(alphaTokens(Account),2) b,arrayStringConcat(extractAll(Account, ‘[0-9]’),’’) c from finance_new_result.profit_result
clickhouse截取字符串

select arrayStringConcat(extractAll(‘AM396子-DE’, ‘[0-9]’),’’) =>396 正则取数字

select replaceRegexpAll(‘AM396子-DE’, ‘[^\w-]’, ‘’) AS e =>AM396-DE 正则去中文

with
‘[土耳其里拉]数据修改:exchange_rate(0.78)=>(0.7752); updated_by()=>(1135); updated_at()=>(2021-09-06 17:34:41); ’ as log_info
select replaceRegexpAll(arrayElement(splitByChar(’;’, log_info), 1), ‘[^\d.=]’, ‘’) a,
splitByChar(’=’,replaceRegexpAll(arrayElement(splitByChar(’;’, log_info), 1), ‘[^\d.=]’, ‘’)) b,
arrayElement(splitByChar(’=’,replaceRegexpAll(arrayElement(splitByChar(’;’, log_info), 1), ‘[^\d.=]’, ‘’)),1) c,
arrayElement(splitByChar(’=’,replaceRegexpAll(arrayElement(splitByChar(’;’, log_info), 1), ‘[^\d.=]’, ‘’)),2) d
=》0.78=0.7752 [‘0.78’,‘0.7752’] 0.78 0.7752
取多个数字并拆分（正则表达式）

11.正则表达式匹配括号里的内容包括括号
[(|（].*[)|）]$

1.取店长变化
with ‘

店长 (2522) 变更为 (2499);

更新人 变更为 (2522);

更新时间 (2021-12-21 19:41:36) 变更为 (2021-12-21 19:42:52);

’ as log_info
select *,arrayElement(splitByChar(’;’,log_info),1)
,replaceRegexpAll(arrayElement(splitByChar(’;’,log_info),1),’[^\d.)]’, ‘’)
,splitByChar(’)’,replaceRegexpAll(arrayElement(splitByChar(’;’,log_info),1),’[^\d.)]’, ‘’))
,arrayElement(splitByChar(’)’,replaceRegexpAll(arrayElement(splitByChar(’;’,log_info),1),’[^\d.)]’, ‘’)),1)
,arrayElement(splitByChar(’)’,replaceRegexpAll(arrayElement(splitByChar(’;’,log_info),1),’[^\d.)]’, ‘’)),2)
–from finance_new.base_store_log where log_info like ‘%

店长%’
2.取店长变化，先定位店长位置再截取
with ‘

默认品牌 变更为 (Lshan);

店长 (1744) 变更为 (2662);

更新人 (2) 变更为 (861);

更新时间 (2021-12-23 11:56:36) 变更为 (2021-12-29 09:13:12);

Gross Margin 变更为 (25);

’ as log_info1
select log_info1
,toInt64OrZero(arrayElement(splitByChar(’)’,replaceRegexpAll(arrayElement(splitByChar(’;’,log_info1),1),’[^\d.)]’, ‘’)),1)) a
,toInt64OrZero(arrayElement(splitByChar(’)’,replaceRegexpAll(arrayElement(splitByChar(’;’,log_info1),1),’[^\d.)]’, ‘’)),2)) b
,splitByChar(’;’,log_info1) c
,substr(log_info1,position(log_info1, ‘

店长’),60) d
,position(log_info1, ‘

店长’) e
,toInt64OrZero(arrayElement(splitByChar(’)’,replaceRegexpAll(substr(log_info1,position(log_info1, ‘

店长’),60),’[^\d.)]’, ‘’)),1)) f
,toInt64OrZero(arrayElement(splitByChar(’)’,replaceRegexpAll(substr(log_info1,position(log_info1, ‘

店长’),60),’[^\d.)]’, ‘’)),2)) g

clickhouse更新数据（用ReplacingMergeTree引擎）
create table finance_new.amz_list (
OrderSourceTypeName String COMMENT ‘来源渠道类型名称’,
OrderSourceName String COMMENT ‘来源渠道名称’,
OrderSourceSKU String COMMENT ‘渠道SKU’,
SKU String COMMENT ‘系统SKU’,
ClientSKU Nullable(String) COMMENT ‘自定义SKU’,
ASIN Nullable(String) COMMENT ‘ASIN码(只亚马逊平台)’,
OrderSourceType String COMMENT ‘来源渠道类型名称’,
AddAdminName Nullable(String),
BusinessAdminName Nullable(String),
ParentOrderSourceSKU Nullable(String),
ParentASIN Nullable(String),
RankNum Nullable(String),
RankZTNum Nullable(String),
RankQTNum Nullable(String),
RankSQNum Nullable(String),
create_time DateTime
)
ENGINE = ReplacingMergeTree(create_time)
PARTITION BY(OrderSourceTypeName)
order by (OrderSourceTypeName,OrderSourceName,OrderSourceSKU)

truncate table finance_new.amz_list
insert into finance_new.amz_list select *,now() create_time from finance_new.amz_saihe_sku_list

insert into finance_new.amz_list select *,toDateTime(‘2021-08-08 00:00:00’) create_time from finance_new.amz_saihe_sku_list where SKU=‘1000104’

optimize table finance_new.amz_list final;

clickhouse复杂时间格式转换
select parseDateTimeBestEffort(‘1 Nov 2021 01:19:14 UTC’)；–有时区
2021-11-01 09:19:14
select parseDateTimeBestEffort(‘1 Nov 2021 01:19:14’)；–没有时区
2021-11-01 01:19:14

开窗函数
,groupArray(financial_event_group_end) AS arr_val
,arrayEnumerate(arr_val) AS row_number
,arrayElement(groupArray(1)(log_date),1) AS arr_val
,arrayElement(groupArray(1)(original_amount),1)
arraySum(groupArray(1)(original_to_rmb)) original_to_rmb,
arraySum(groupArray(1)(originalrmb_to_usd)) originalrmb_to_usd