Clickhouse 实战记录 - MrSnake 博客 - 记录与分享

查询订单中当前月之前没有出现过的顾客的数量

即新顾客的数量

思路：

首先创建一张临时表，存放每个顾客首次购买的时间
顾客表与这张临时表进行联表查询
判断条件：
- 新顾客，某一行的数据日期 = 首次购买日期
- 老顾客，某一行的数据日期 > 首次购买日期
- 全部顾客，某一行的数据日期 >= 首次购买日期
- 其中，全部顾客 = 新顾客 + 老顾客

mysql数据库映射到clickhouse

一般来讲，业务数据都存放在 mysql 中，而要对业务数据做分析做分析的话，需要将 mysql 中的数据映射到 clickhouse，在进行分析。

映射 mysql 数据库的方式，非常的方便，例如：

-- 将 host 为 192.168.1.10:3306 mysql 的 oneline_pd 数据库
-- 通过用户 root 密码 123456 映射到 clickhouse 中，并命名为 online_pd
create database online_pd engine = MySQL('192.168.1.10:3306', 'online_pd', 'root', '123456')

先将数据入库到 ClickHouse 再进行查询

需要注意的是，直接对映射过来的 mysql 中表进行查询的话是用不了索引的，会非常的满，需要先将数据 “load” 进 clickhouse 中存储引擎选择 MergeTree()，满足大部分需求。

-- 如下，将 coupons 导入 clickhouse 中存储引擎 MergeTree()
drop table if exists tmp_coupons;
create table tmp_coupons engine = MergeTree() order by id
as
select * from online_pd.coupons;

解决步骤

1.首先创建一张临时表，存放每个顾客首次购买的时间

-- 存着每个顾客的首次购买时间
drop table if exists tmp_customers_first_purchase_time;
create table tmp_customers_first_purchase_time(
    `username` String,
    `first_purchase_time` Date,
) engine = MergeTree order by username;
-- 插入数据
-- tmp_customer_month_buy 顾客的每个月消费情况
insert into tmp_customers_first_purchase_time
select username
             min(buy_time) AS first_purchase_time
from tmp_customer_month_buy
group by username

2.顾客表与这张临时表进行联表查询，并进行判断得出结果

-- 创建存放新老顾客的表
drop table if exists tmp_customers_new_old;
create table tmp_customers_new_old(
  `username` String comment '用户名',
  `date` Date comment '购买月份',
  `new_customer` Int32 comment '新客户数',
  `old_customer` Int32 comment '老客户数',
  `all_customer` Int32 comment '全部客户'
) engine = MergeTree order by (shop_no, date);

-- 进行判断插入数据
INSERT INTO tmp_customers_new_old
SELECT mb1.name                                              AS name,
       mb1.date                                              AS date,
       -- 购买月份 = 首次购买日期 新顾客
       countIf(Distinct mb1.customer_no, created_at = date)  as new_customers,
       -- 购买月份 < 首次购买日期 老顾客
       countIf(Distinct mb1.customer_no, created_at < date)  as old_customers,
       -- 购买月份 <= 首次购买日期 全部顾客 = 新顾客 + 老顾客
       countIf(Distinct mb1.customer_no, created_at <= date) as all_customers
FROM tmp_customer_month_buy as mb1
     join tmp_customers_first_purchase_time as mb2 on mb1.username = mb2.username
group by mb1.name,

利用 clickhouse 内置函数进行 aes-256-cbc 加密

-- 语法
encrypt('mode', 'plaintext', 'key' [, iv, aad])

iv 初始化向量
add 额外的身份验证数据

加密

-- 输入16位偏移量
select encrypt('aes-256-cbc', 'mrsnake', '12345678910121314151617181920212', '1234567891012131')