clickhouse

已于 2023-10-19 15:08:11 修改
阅读量727 收藏 1
点赞数
文章标签： clickhouse
于 2023-10-19 15:02:16 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gogler/article/details/133926427
版权

官方链接

<insert id="insertTable" parameterType="com.ioc.orm.ck.model.TableModel">
    insert into table_name
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="ts != null">
            ts,
        </if>
        <if test="deviceId != null">
            device_id,
        </if>
        <if test="driverId != null">
            driver_id,
        </if>
        <if test="longitude != null">
            longitude,
        </if>
        <if test="latitude != null">
            latitude,
        </if>
        <if test="altitude != null">
            altitude,
        </if>
        <if test="direction != null">
            direction,
        </if>
        <if test="mileage != null">
            mileage,
        </if>
        <if test="rssi != null">
            rssi,
        </if>
        <if test="satellites != null">
            satellites,
        </if>
        <if test="alarm != null">
            alarm,
        </if>
        <if test="signalStatus != null">
            signal_status,
        </if>
        <if test="gmtCreate != null">
            gmt_create,
        </if>
        <if test="kind != null">
            kind,
        </if>
        <if test="oil != null">
            oil,
        </if>
        <if test="plate != null and plate!=''">
            plate,
        </if>
        <if test="deviceGroupId != null">
            device_group_id,
        </if>
        <if test="acc != null">
            acc,
        </if>
        <if test="status != null">
            status,
        </if>
        <if test="mobile != null and mobile!=''">
            mobile,
        </if>
        <if test="provinceId != null">
            province_id,
        </if>
        <if test="cityId != null">
            city_id,
        </if>
        <if test="districtId != null">
            district_id,
        </if>
        <if test="address != null and address!=''">
            address,
        </if>
        <if test="bdLongitude != null">
            bd_longitude,
        </if>
        <if test="bdLatitude != null">
            bd_latitude,
        </if>
        <if test="gdLongitude != null">
            gd_longitude,
        </if>
        <if test="gdLatitude != null">
            gd_latitude,
        </if>
        <if test="speed != null">
            speed,
        </if>
        <if test="speed2 != null">
            speed2,
        </if>
        <if test="mileage2 != null">
            mileage2
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="ts != null">
            #{ts},
        </if>
        <if test="deviceId != null">
            #{deviceId},
        </if>
        <if test="driverId != null">
            #{driverId},
        </if>
        <if test="longitude != null">
            #{longitude},
        </if>
        <if test="latitude != null">
            #{latitude},
        </if>
        <if test="altitude != null">
            #{altitude},
        </if>
        <if test="direction != null">
            #{direction},
        </if>
        <if test="mileage != null">
            #{mileage},
        </if>
        <if test="rssi != null">
            #{rssi},
        </if>
        <if test="satellites != null">
            #{satellites},
        </if>
        <if test="alarm != null">
            #{alarm},
        </if>
        <if test="signalStatus != null">
            #{signalStatus},
        </if>
        <if test="gmtCreate != null">
            #{gmtCreate},
        </if>
        <if test="kind != null">
            #{kind},
        </if>
        <if test="oil != null">
            #{oil},
        </if>
        <if test="plate != null and plate!=''">
            #{plate},
        </if>
        <if test="deviceGroupId != null">
            #{deviceGroupId},
        </if>
        <if test="acc != null">
            #{acc},
        </if>
        <if test="status != null">
            #{status},
        </if>
        <if test="mobile != null and mobile!=''">
            #{mobile},
        </if>
        <if test="provinceId != null">
            #{provinceId},
        </if>
        <if test="cityId != null">
            #{cityId},
        </if>
        <if test="districtId != null">
            #{districtId},
        </if>
        <if test="address != null and address!=''">
            #{address},
        </if>
        <if test="bdLongitude != null">
            #{bdLongitude},
        </if>
        <if test="bdLatitude != null">
            #{bdLatitude},
        </if>
        <if test="gdLongitude != null">
            #{gdLongitude},
        </if>
        <if test="gdLatitude != null">
            #{gdLatitude},
        </if>
        <if test="speed != null">
            #{speed},
        </if>
        <if test="speed2 != null">
            #{speed2},
        </if>
        <if test="mileage2 != null">
            #{mileage2}
        </if>
    </trim>
--导出CSV，无权限？

SELECT *

FROM table_name

INTO OUTFILE 'out.csv'

FORMAT CSVWithNames



--插入车辆轨迹

INSERT INTO table_name (ts, device_id, driver_id, longitude, latitude, altitude, direction, mileage, rssi, satellites, alarm, signal_status, gmt_create, kind, oil, plate, device_group_id, acc, status, mobile, province_id, city_id, district_id, address, bd_longitude, bd_latitude, gd_longitude, gd_latitude, speed, speed2, mileage2) VALUES('2023-10-09 11:09:10.567', 5451280, 18730, 125.497977, 43.922478, 272, 120, 1212139.1, 4, 31, 0, 0, '2023-10-10 01:40:27', 2, 0.0, '浙A5Z086', 1765, 1, 786435, '14270593027', 220000, 220100, 220105, '吉林省长春市二道区英俊镇', 125.51080260372358, 43.93105415890568, 125.5043880599486, 43.92479494828726, 0.0, 0.0, 0.0);

INSERT INTO table_name (ts, device_id, driver_id, longitude, latitude, altitude, direction, mileage, rssi, satellites, alarm, signal_status, gmt_create, kind, oil, plate, device_group_id, acc, status, mobile, province_id, city_id, district_id, address, bd_longitude, bd_latitude, gd_longitude, gd_latitude, speed, speed2, mileage2) VALUES('2023-10-10 01:37:44', 5452662, 53124, 120.647793, 30.688315, 14, 76, 1211098.1, 4, 28, 0, 0, '2023-10-10 01:38:17', 2, 0.0, '浙A8Z987', 1765, 1, 786435, '14465049051', 330000, 330400, 330411, '浙江省嘉兴市秀洲区洪合镇登发超市东北217米', 120.65852330812179, 30.691511861460846, 120.65195821842615, 30.68584701544459, 0.0, 0.0, 0.0);

INSERT INTO table_name (ts, device_id, driver_id, longitude, latitude, altitude, direction, mileage, rssi, satellites, alarm, signal_status, gmt_create, kind, oil, plate, device_group_id, acc, status, mobile, province_id, city_id, district_id, address, bd_longitude, bd_latitude, gd_longitude, gd_latitude, speed, speed2, mileage2) VALUES('2023-10-10 01:33:10', 5448636, 52822, 123.195897, 41.775379, 53, 242, 423534.6, 4, 33, 0, 0, '2023-10-10 01:35:00', 2, 0.0, '辽LB1869', 1765, 1, 4980739, '14270526232', 210000, 210100, 210114, '辽宁省沈阳市于洪区沙岭街道沈阳中联电气设备有限公司(沙和路)南333米', 123.20830242649883, 41.78353508290512, 123.20173472609495, 41.77768495701079, 72.0, 0.0, 0.0489275);

INSERT INTO table_name (ts, device_id, driver_id, longitude, latitude, altitude, direction, mileage, rssi, satellites, alarm, signal_status, gmt_create, kind, oil, plate, device_group_id, acc, status, mobile, province_id, city_id, district_id, address, bd_longitude, bd_latitude, gd_longitude, gd_latitude, speed, speed2, mileage2) VALUES('2023-10-10 01:43:08', 5447681, 52913, 114.314814, 31.953241, 83, 313, 1201920.4, 5, 28, 0, 0, '2023-10-10 01:43:36', 2, 0.0, '浙A0Z730', 1765, 1, 4980739, '14270593155', 410000, 411500, 411521, '河南省信阳市罗山县灵山镇张楼小学西南489米', 114.32695907373498, 31.95706819668387, 114.32039364601484, 31.951228147198893, 80.0, 0.0, 0.0482153);

INSERT INTO table_name (ts, device_id, driver_id, longitude, latitude, altitude, direction, mileage, rssi, satellites, alarm, signal_status, gmt_create, kind, oil, plate, device_group_id, acc, status, mobile, province_id, city_id, district_id, address, bd_longitude, bd_latitude, gd_longitude, gd_latitude, speed, speed2, mileage2) VALUES('2023-10-10 01:10:02', 5542108, 53123, 114.070679, 31.052708, 48, 194, 228622.8, 4, 35, 0, 0, '2023-10-10 01:10:24', 2, 0.0, '浙A72F17', 0, 1, 4980739, '14270568822', 420000, 420900, 420902, '湖北省孝感市孝南区杨店镇孝昌县三葱文化传媒有限公司东南259米', 114.08300286555479, 31.0564264158298, 114.07646151237014, 31.050654449671725, 90.0, 89.0, 0.0543662);

INSERT INTO table_name(ts, device_id, driver_id, longitude, latitude, altitude, direction, mileage, rssi, satellites, alarm, signal_status, gmt_create, kind, oil, plate, device_group_id, acc, status, mobile, province_id, city_id, district_id, address, bd_longitude, bd_latitude, gd_longitude, gd_latitude, speed, speed2, mileage2) VALUES('2023-10-10 01:54:03', 5540470, 53371, 112.057787, 32.107862, 53, 59, 433417.2, 0, 0, 0, 0, '2023-10-10 06:23:53', 2, 0.0, '鄂ADC062', 1758, 0, 786434, '14267957981', 420000, 420600, 420606, '湖北省襄阳市樊城区牛首镇襄阳双虎粮油北166米', 112.07029240384568, 32.11207992510232, 112.06386214037852, 32.10585844164688, 0.0, 0.0, 0.0388412);

INSERT INTO table_name (ts, device_id, driver_id, longitude, latitude, altitude, direction, mileage, rssi, satellites, alarm, signal_status, gmt_create, kind, oil, plate, device_group_id, acc, status, mobile, province_id, city_id, district_id, address, bd_longitude, bd_latitude, gd_longitude, gd_latitude, speed, speed2, mileage2) VALUES('2023-10-10 01:07:17', 431, 0, 117.440221, 39.122015, 44, 16, 241156.3, 27, 0, 0, 0, '2023-10-10 01:21:27', 2, 0.0, '浙A7Y317', 1739, 0, 2, '13955922198', 120000, 120100, 120110, '天津市东丽区军粮城街道', 117.45327611753122, 39.129101067458585, 117.44675313519525, 39.12315963816809, 0.0, 0.0, 0.0);

INSERT INTO table_name (ts, device_id, driver_id, longitude, latitude, altitude, direction, mileage, rssi, satellites, alarm, signal_status, gmt_create, kind, oil, plate, device_group_id, acc, status, mobile, province_id, city_id, district_id, address, bd_longitude, bd_latitude, gd_longitude, gd_latitude, speed, speed2, mileage2) VALUES('2023-10-10 00:14:49', 5542154, 57466, 121.481904, 38.965008, 82, 307, 149958.3, 5, 30, 524288, 0, '2023-10-10 00:16:52', 2, 0.0, '京AVC721', 0, 0, 786434, '14449802955', 210000, 210200, 210211, '辽宁省大连市甘井子区辛寨子街道世亚食品有限公司东北140米', 121.49374431242279, 38.971754503842966, 121.48717144677192, 38.96604576911434, 0.0, 0.0, 0.0);

INSERT INTO table_name (ts, device_id, driver_id, longitude, latitude, altitude, direction, mileage, rssi, satellites, alarm, signal_status, gmt_create, kind, oil, plate, device_group_id, acc, status, mobile, province_id, city_id, district_id, address, bd_longitude, bd_latitude, gd_longitude, gd_latitude, speed, speed2, mileage2) VALUES('2023-10-09 09:03:16', 4123188, 206228, 0.0, 0.0, 34, 0, 0.0, 0, 0, 0, 0, '1970-01-01 00:00:00', 0, 0.0, '', 0, 0, 0, '', 0, 0, 0, '山东省济宁市任城区接庄街道山东济宁九星无纺材料有限公司东南262米', 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0);

INSERT INTO table_name (ts, device_id, driver_id, longitude, latitude, altitude, direction, mileage, rssi, satellites, alarm, signal_status, gmt_create, kind, oil, plate, device_group_id, acc, status, mobile, province_id, city_id, district_id, address, bd_longitude, bd_latitude, gd_longitude, gd_latitude, speed, speed2, mileage2) VALUES('2023-10-09 12:57:27', 5455275, 53167, 117.1148, 36.873241, 13, 290, 842586.9, 4, 28, 0, 0, '2023-10-09 12:57:33', 2, 0.0, '浙A29A29', 1765, 0, 786434, '14270596428', 370000, 370100, 370115, '山东省济南市济阳区崔寨街道', 117.12736400914778, 36.88018065214984, 117.12095579687728, 36.873823843206324, 0.0, 0.0, 0.0);

--车辆最新轨迹点，9s

SELECT distinct *

FROM table_name

WHERE (device_id , ts) IN (

SELECT

device_id,

max(ts) as ts

FROM table_name

GROUP BY device_id

)

SELECT *

FROM table_name

WHERE (device_id , ts) IN (

SELECT

device_id,

max(ts) as ts

FROM table_name

GROUP BY device_id

)

--车辆最新轨迹点，24s

SELECT * FROM

(

SELECT

device_id,

max(ts) AS ts

FROM table_name

GROUP BY device_id

) a

INNER JOIN table_name b ON a.ts=b.ts AND a.device_id=b.device_id



--3天以内车速为0的轨迹点，15s

SELECT *

FROM table_name

WHERE device_id = 5540465

AND ts >= '2023-10-09 19:00:00'

AND ts <= '2023-10-11 09:21:21'

AND speed=0

AND speed2=0

ORDER BY ts

--历史轨迹，10s

select *

from table_name

where device_id = 5540465

AND ts >= '2023-10-09 19:00:00'

AND ts <= '2023-10-11 09:21:21'

--轨迹点数量，6s

select count(1) as total

from table_name

where device_id = 5540465

AND ts >= '2023-10-09 19:00:00'

AND ts <= '2023-10-11 09:21:21'



--车辆小时里程统计，1s

SELECT device_id as deviceId,

(last(mileage) - first(mileage)) as mileage,

(last(mileage2) - first(mileage2)) as mileage2,

first(mileage) firstMile,

last(mileage) lastMile,

first(mileage2) firstMile2,

last(mileage2) lastMile2

FROM table_name

where ts >= '2023-10-09 19:00:00'

AND ts <= '2023-10-11 09:21:21'

AND mileage > 0

group by device_id



SELECT device_id as deviceId,

first_value(mileage) firstMile,

last_value(mileage) lastMile,

first_value(mileage2) firstMile2,

last_value(mileage2) lastMile2

FROM table_name

where ts >= '2023-10-09 19:00:00'

AND ts <= '2023-10-11 09:21:21'

group by device_id

攻城无数
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
ClickHouse 实战：ClickHouse 安装与启动
SmartSi
05-05 938
ClickHouse 可以在任何具有 x86_64、AArch64 或 PowerPC64LE CPU 架构的 Linux、FreeBSD 或 Mac OS X 上运行。官方预构建的二进制文件通常针对 x86_64 进行编译并利用 SSE 4.2 指令集
ClickHouse宝典
探索大数据世界 - 深入解析数据存储、分布式计算与人工智能
01-01 2165
ClickHouse-实操宝典
ClickHouse
shan19920501的博客
06-12 6586
ClickHouse
Clickhouse
qq_51384368的博客
05-15 1981
与mysql类似，表引擎决定其特征，clickhouse不同引擎之间区别很大，分为六类20多种MergeTree系列引擎外部存储类型文件类型内存类型接口类型其它类型其中MergeTree系列引擎性能强大、使用场景广，也只有MergeTree系列引擎才支持主键索引、数据分区、数据副本和数据采样这些特性MergeTree。
ClickHouse安装
m0_54357806的博客
03-03 1237
ClickHouse安装
Clickhouse部署实现
wpwdm159357的博客
05-28 1521
clickhouse部署实现
clickhouse升级
qyq88888的专栏
04-30 717
参考链接：clickhouse 版本升级(rpm 安装包)_软件包 clickhouse-common-static-22.2.2.1-2.x86_64.rpm-CSDN博客
ClickHouse 使用技巧总结
奔跑的蜗牛的博客
05-30 1511
本文对 Clickhouse 的常用使用技巧进行了总结，包括数据导入导出，建表技巧，如何创建表才能真正提升查询速度，最后介绍了一些高级的技巧，包括投影和物化视图的使用，以及在数据量特别大的时候，使用位图来进行高效的存储和位运算。
ClickHouse访问
lidepan的博客
08-17 1308
ClickHouse的访问方式
ClickHouse中文文档
04-11
"ClickHouse中文文档" ClickHouse是⼀一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。它的设计目的是为了适应OLAP场景的需求，具有高效的数据处理和存储能力。 列式数据库管理系统的特点是将数据按列存储，而...
Datax支持clickhouse
08-05
在标题提到的"Datax支持clickhouse"，意味着Datax已经扩展了对ClickHouse数据库的兼容性，使得我们可以利用Datax将数据从其他数据源导入到ClickHouse中。 ClickHouse是一款高性能的列式存储数据库管理系统（MPP），...
clickhouse安装包
01-19
ClickHouse是一款高性能、分布式列式数据库管理系统（Column-Oriented DBMS），主要应用于在线分析处理（OLAP）场景。它的设计目标是实现高并发查询、快速数据处理和大规模数据存储。在本文中，我们将深入探讨...
clickhouse.zip
09-22
Clickhouse是一款高性能的开源列式数据库管理系统（Column-Oriented DBMS），主要设计用于在线分析处理（OLAP）和实时大数据分析。它以其快速查询、高并发读取和大规模数据处理能力而闻名。在本压缩包`clickhouse....
clickhouse高可用&可拓展部署
最新发布
weixin_41745141的博客
07-05 726
本文介绍了clickhouse的高可用和可扩展架构的部署
ruoyi mybatis pagehelper 分页优化（自定义limit位置）clickhouse 外部数据源
vransy的博客
07-04 263
自定义limit
使用Ckman部署ClickHouse集群介绍
wt334502157的博客
07-03 798
ClickHouse Manager是一个为ClickHouse数据库量身定制的管理工具，它是由擎创科技数据库团队主导研发的一款用来管理和监控ClickHouse集群的可视化运维工具。目前该工具已在github上开源，开源地址为：github.com/housepower/ckman。它通过提供一个直观的图形用户界面(GUI)，极大地简化了数据库的管理和操作。这款工具支持多种功能，包括但不限于SQL查询编辑器、数据可视化、实时监控、性能分析、备份与恢复等。
clickhouse-client 数据导入导出
zlhblogs的博客
07-02 260
有时需要导入别人给的几百个文件，可以选择使用xargs批量导入。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值