建设部网站2015年第158号,泰安58路,网站简单设计,建网站的软件优帮云一、ClickHouseSpringBoot2.XMybatisPlus整合搭建
二、需求描述和数据库准备 三、ClickHouse统计SQL编写实战和函数使用
四、ClickHouseSpringBoot2.X案例-基础模块搭建
controller/request层
mapper层 model层 service层 五、ClickHouseSpringBoot2.X案例-数据统计接口 …一、ClickHouseSpringBoot2.XMybatisPlus整合搭建
二、需求描述和数据库准备 三、ClickHouse统计SQL编写实战和函数使用
四、ClickHouseSpringBoot2.X案例-基础模块搭建
controller/request层
mapper层 model层 service层 五、ClickHouseSpringBoot2.X案例-数据统计接口
service层
mapper层
一、ClickHouseSpringBoot2.XMybatisPlus整合搭建
1.在线创建项目 https://start.spring.io/ idea导入刚下载下来的项目 在pom.xml中增加ClickHouse依赖 dependencygroupIdru.yandex.clickhouse/groupIdartifactIdclickhouse-jdbc/artifactIdversion0.1.55/version/dependency!--mybatis plus--dependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-boot-starter/artifactIdversion3.4.0/version/dependency 数据库连接配置
server.port8080
spring.datasource.driver-class-nameru.yandex.clickhouse.ClickHouseDriver
spring.datasource.urljdbc:clickhouse://11x.xxx.xx.24x:8123/default
mybatis-plus.configuration.log-implorg.apache.ibatis.logging.stdout.StdOutImpl
logging.level.rootINFO
二、需求描述和数据库准备
指定商品统计指定日期范围内各个省市的访问的pv数量如7天内有各个城市访问某个商品分布
指定商品多天内的访问曲线变化图如1~7号商品访问量波动图
建表语句
CREATE TABLE default.visit_stats
(product_id UInt64,//商品idis_new UInt16,//是否是新用户 1新用户 0老用户province String,//省名称city String,//市名称pv UInt32,//轻度聚合后的访问量visit_time DateTime //访问时间
)
ENGINE MergeTree()
PARTITION BY toYYYYMMDD(visit_time)
ORDER BY (product_id,is_new,province,city);
插入sql
INSERT into visit_stats values
(1,1,广东,广州,14323,2023-01-01 12:11:13),
(1,0,广东,广州,4232,2023-02-12 16:16:13),
(1,1,广东,佛山,54323,2023-03-06 16:11:13),
(1,0,广东,东莞,42341,2023-03-02 16:12:13),
(1,1,广东,梅州,52422,2023-03-09 12:11:13),
(2,1,广东,广州,14323,2021-03-01 12:11:13),
(2,0,广东,深圳,425232,2023-04-12 16:16:13),
(2,1,广东,佛山,543323,2022-06-06 16:11:13),
(2,0,广东,东莞,42341,2021-05-02 16:12:13),
(2,1,广东,梅州,52422,2022-01-09 12:11:13),
(3,1,北京,北京,13132,2023-01-01 12:11:13),
(3,0,广东,广州,533232,2022-02-16 16:16:13),
(4,1,浙江,杭州,663643,2023-12-06 12:11:13),
(4,0,广东,东莞,4142,2023-11-02 16:12:13),
(5,1,湖南,长沙,52123,2022-01-09 12:11:13),
(4,0,湖南,衡阳,4142,2024-05-02 16:12:13),
(5,1,广东,中山,52123,2024-01-09 12:11:13),
(2,1,上海,上海,14323,2021-03-01 12:11:13),
(5,0,浙江,宁波,425232,2023-04-12 16:16:13),
(3,1,广东,佛山,543323,2022-06-06 16:11:13),
(2,0,湖南,长沙,42341,2021-05-02 16:12:13),
(2,1,广东,深圳,52422,2022-01-09 12:11:13) 三、ClickHouse统计SQL编写实战和函数使用
统计需求:某个商品再时间范围内地区访问分布-城市级别天级别
select province,city, sum(pv) pv_count
from visit_stats where product_id 1
and toYYYYMMDD(visit_time) BETWEEN 20200101 and 20241212
group by province,city order by pv_count desc 函数: 求和:sum(pv) 年格式:select toYear(toDateTime(2024-12-11 11:12:13)) 日期格式化:select toYYYYMMDD(toDateTime(2024-12-11 11:12:13)) 日期时间格式化select toYYYYMMDDhhmmss(toDateTime(2024-12-11 11:12:13)) 周格式化,1~7当前时间是本周第几天下面是周三结果是3周日结果是7 select toDayOfWeek(toDateTime(2024-12-11 11:12:13)) 小时格式化提取时间里面的小时比如 2023-12-29 10:05:10格式化后是【10】点 select toHour(toDateTime(2024-12-11 11:12:13)) 分钟格式化提取时间里面的分钟比如 2023-12-29 10:05:10格式化后是【5】分钟 select toMinute(toDateTime(2024-12-11 11:12:13)) 秒格式化提取时间里面的秒 select toSecond(toDateTime(2024-12-11 11:12:13)) 获取当前日期时间 select now() 获取当前日期 select today() 某个商品多天内的访问曲线图, 天级别 select toYYYYMMDD(visit_time) date_time_str, sum(pv) pv_count from visit_statswhere product_id 2 and toYYYYMMDD(visit_time) BETWEEN 20200101 and 20241212 group by date_time_str ORDER BY date_time_str desc 所用函数: 逻辑判断: SELECT if(cond, then, else) 例子SELECT if(1, plus(3, 3), plus(6, 8)) 如果条件 cond 的计算结果为非零值则返回表达式 then 的结果并且跳过表达式 else 的结果 如果 cond 为零或 NULL则将跳过 then 表达式的结果并返回 else 表达式的结果 字符串拼接(不能双引号): select concat(我,上班的时候,没有摸鱼~) 最大、最小、平均值: select max(pv), min(pv), avg(pv) from visit_stats 四、ClickHouseSpringBoot2.X案例-基础模块搭建
controller/request层
package net.wnnck.demo.controller.request;public class VisitRecordPageRequest {private long productId;private int page;private int size;public long getProductId() {return productId;}public void setProductId(long productId) {this.productId productId;}public int getPage() {return page;}public void setPage(int page) {this.page page;}public int getSize() {return size;}public void setSize(int size) {this.size size;}
}package net.wnnck.demo.controller;import net.wnnck.demo.controller.request.VisitRecordPageRequest;
import net.wnnck.demo.model.JsonData;
import net.wnnck.demo.service.VisitStatsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.Map;
RestController
RequestMapping(/api/v1/data)
public class DataController {Autowiredprivate VisitStatsService visitStatsService;RequestMapping(page)public JsonData queryVisitRecord(RequestBody VisitRecordPageRequest pageRequest){MapString, Object map visitStatsService.pageVisitRecord(pageRequest);return JsonData.buildSuccess(map);}}mapper层
package net.wnnck.demo.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import net.wnnck.demo.model.VisitStatsDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;import java.util.List;Mapper
public interface VisitStatsMapper extends BaseMapperVisitStatsDO {/*** 统计总条数* param productId* return*/int countTotal(Param(productId) long productId);/*** 分页* param from* param size* return*/ListVisitStatsDO pageVisitRecord(Param(productId)Long productId , Param(from) int from, Param(size) int size);}resources/mapper/VisitStatsMapper.xml?xml version1.0 encodingUTF-8?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacenet.wnnck.demo.mapper.VisitStatsMapper!-- 通用查询映射结果 --resultMap idBaseResultMap typenet.wnnck.demo.model.VisitStatsDOresult columnproduct_id propertyproductId/result columnis_new propertyisNew/result columnprovince propertyprovince/result columncity propertycity/result columnpv propertypv/result columnvisit_time propertyvisitTime//resultMap!-- 通用查询结果列 --sql idBase_Column_Listproduct_id,is_new,province,city,pv,visit_time/sql!--统计总条数--select idcountTotal resultTypejava.lang.Integerselect count(1) from visit_stats where product_id#{productId}/select!--分页查找--select idpageVisitRecord resultMapBaseResultMapselectinclude refidBase_Column_List/from visit_stats where product_id#{productId}order by visit_time desc limit #{from},#{size}/select/mapper model层
package net.wnnck.demo.model;public class JsonData {/*** 状态码 0 表示成功*/private Integer code;/*** 数据*/private Object data;/*** 描述*/private String msg;public JsonData(){}public JsonData(Integer code, Object data, String msg) {this.code code;this.data data;this.msg msg;}/*** 成功不传入数据** return*/public static JsonData buildSuccess() {return new JsonData(0, null, null);}/*** 成功传入数据** param data* return*/public static JsonData buildSuccess(Object data) {return new JsonData(0, data, null);}/*** 失败传入描述信息** param msg* return*/public static JsonData buildError(String msg) {return new JsonData(-1, null, msg);}/*** 自定义状态码和错误信息** param code* param msg* return*/public static JsonData buildCodeAndMsg(int code, String msg) {return new JsonData(code, null, msg);}public Integer getCode() {return code;}public void setCode(Integer code) {this.code code;}public Object getData() {return data;}public void setData(Object data) {this.data data;}public String getMsg() {return msg;}public void setMsg(String msg) {this.msg msg;}
}package net.wnnck.demo.model;public class VisitStatsDO {/*** 商品*/private Long productId;/*** 访问时间*/private String visitTime;/*** 1是新访客0是老访客*/private Integer isNew;/*** 访问量*/private Integer pv;/*** 省份*/private String province;/*** 城市*/private String city;/*** 度量值*/private Long pvCount0L;/*** 时间的字符串映射天、小时*/private String dateTimeStr;public Long getProductId() {return productId;}public void setProductId(Long productId) {this.productId productId;}public String getVisitTime() {return visitTime;}public void setVisitTime(String visitTime) {this.visitTime visitTime;}public Integer getIsNew() {return isNew;}public void setIsNew(Integer isNew) {this.isNew isNew;}public Integer getPv() {return pv;}public void setPv(Integer pv) {this.pv pv;}public String getProvince() {return province;}public void setProvince(String province) {this.province province;}public String getCity() {return city;}public void setCity(String city) {this.city city;}public Long getPvCount() {return pvCount;}public void setPvCount(Long pvCount) {this.pvCount pvCount;}public String getDateTimeStr() {return dateTimeStr;}public void setDateTimeStr(String dateTimeStr) {this.dateTimeStr dateTimeStr;}
}service层
package net.wnnck.demo.service;import net.wnnck.demo.controller.request.VisitRecordPageRequest;import java.util.Map;public interface VisitStatsService {MapString,Object pageVisitRecord(VisitRecordPageRequest pageRequest);
}
package net.wnnck.demo.service.impl;import net.wnnck.demo.controller.request.VisitRecordPageRequest;
import net.wnnck.demo.mapper.VisitStatsMapper;
import net.wnnck.demo.model.VisitStatsDO;
import net.wnnck.demo.service.VisitStatsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.HashMap;
import java.util.List;
import java.util.Map;Service
public class VisitStatsServiceImpl implements VisitStatsService {Autowiredprivate VisitStatsMapper visitStatsMapper;Overridepublic MapString, Object pageVisitRecord(VisitRecordPageRequest pageRequest) {MapString,Object data new HashMap(3);Long productId pageRequest.getProductId();int page pageRequest.getPage();int size pageRequest.getSize();int count visitStatsMapper.countTotal(productId);int from (page -1) * size;ListVisitStatsDO visitStatsDOS visitStatsMapper.pageVisitRecord(productId, from, size);data.put(total,count);data.put(current_page,page);data.put(data,visitStatsDOS);/*** 计算总页数*/int totalPage;if(count % size 0){totalPage count / size;}else {totalPage count / size 1 ;}data.put(total_page,totalPage);return data;}
}启动后可正常访问表示基础环境已搭建好。 文末有贴代码下载地址~ 五、ClickHouseSpringBoot2.X案例-数据统计接口 第三节的需求sql整合mapper
service层 Overridepublic ListVisitStatsDO queryVisitTrend(VisitTrendQueryRequest queryRequest) {Long productId queryRequest.getProductId();String type queryRequest.getType();ListVisitStatsDO list null;if(type.equalsIgnoreCase(region)){list visitStatsMapper.queryRegionTrendWithMultiDay(productId,queryRequest.getStartTime(),queryRequest.getEndTime());} else if(type.equalsIgnoreCase(day)){list visitStatsMapper.queryVisitTrendWithMultiDay(productId,queryRequest.getStartTime(),queryRequest.getEndTime());}return list;}
mapper层
select idqueryRegionTrendWithMultiDay resultMapBaseResultMapselect province ,city, sum(pv) pv_count from visit_stats
where product_id #{productId} and toYYYYMMDD(visit_time) BETWEEN #{startTime} and #{endTime}
GROUP BY province ,city order by pv_count desc
/selectselect idqueryVisitTrendWithMultiDay resultMapBaseResultMapselect toYYYYMMDD(visit_time) date_time_str,sum(pv) pv_count from visit_statswhere product_id #{productId} and toYYYYMMDD(visit_time) BETWEEN #{startTime} and #{endTime}GROUP BY date_time_str order by date_time_str desc/select #时间范围内地区访问分布-城市级别 多天内的访问曲线图pv 代码下载地址:
链接https://pan.baidu.com/s/1g8dHKiZMQIhJTmuCO814hw?pwdex2x 提取码ex2x
ClickHouse快速安装-可视化工具连接-创建第一个ck库表一_clickhouse可视化工具_这是王姑娘的微博的博客-CSDN博客OLAP是什么以及快速安装ClickHouse(容器化部署),CK可视化工具的下载链接使用以及创建第一个CK数据库和表然后新增数据浏览3分钟即可快速掌握这些知识https://blog.csdn.net/wnn654321/article/details/125837194ClickHouse常见SQL语法和常见合并数引擎Demo(二)_这是王姑娘的微博的博客-CSDN博客分区是表的分区把一张表的数据分成N多个区块分区后的表还是一张表数据处理还是由自己来完成PARTITION BY指的是一个表按照某一列数据比如日期进行分区不同分区的数据会写入不同的文件中建表时加入partition概念可以按照对应的分区字段允许查询在指定了分区键的条件下尽可能的少读取数据注意:不是所有的表引擎都可以分区合并树(MergeTree) 系列的表引擎才支持数据分区Log系列引擎不支持。...https://blog.csdn.net/wnn654321/article/details/125920177
