clickhouse

最新推荐文章于 2024-09-23 16:06:43 发布

KANGBboy

最新推荐文章于 2024-09-23 16:06:43 发布

阅读量3.5k

点赞数

文章标签：大数据 visual studio code big data

本文链接：https://blog.csdn.net/KANGBboy/article/details/120039050

版权

本文介绍了ClickHouse中使用正则表达式提取数字的方法，如`extractAll`和`replaceRegexpAll`，以及如何截取、拆分字符串和处理复杂时间格式。还展示了如何通过正则匹配获取特定信息，如店长变动，并讲解了数据更新和时间序列处理的技巧。

摘要由CSDN通过智能技术生成

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