clickhouse

官方链接

<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_nameINTO 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);--车辆最新轨迹点，9sSELECT distinct *FROM table_nameWHERE (device_id , ts) IN (SELECTdevice_id,max(ts) as tsFROM table_nameGROUP BY device_id)SELECT *FROM table_nameWHERE (device_id , ts) IN (SELECTdevice_id,max(ts) as tsFROM table_nameGROUP BY device_id)--车辆最新轨迹点，24sSELECT * FROM(SELECTdevice_id,max(ts) AS tsFROM table_nameGROUP BY device_id) aINNER JOIN table_name b ON a.ts=b.ts AND a.device_id=b.device_id--3天以内车速为0的轨迹点，15sSELECT *FROM table_nameWHERE device_id = 5540465AND ts >= '2023-10-09 19:00:00'AND ts <= '2023-10-11 09:21:21'AND speed=0AND speed2=0ORDER BY ts--历史轨迹，10sselect *from table_namewhere device_id = 5540465AND ts >= '2023-10-09 19:00:00'AND ts <= '2023-10-11 09:21:21'--轨迹点数量，6sselect count(1) as totalfrom table_namewhere device_id = 5540465AND ts >= '2023-10-09 19:00:00'AND ts <= '2023-10-11 09:21:21'--车辆小时里程统计，1sSELECT 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) lastMile2FROM table_namewhere ts >= '2023-10-09 19:00:00'AND ts <= '2023-10-11 09:21:21'AND mileage > 0group by device_idSELECT device_id as deviceId,first_value(mileage) firstMile,last_value(mileage) lastMile,first_value(mileage2) firstMile2,last_value(mileage2) lastMile2FROM table_namewhere ts >= '2023-10-09 19:00:00'AND ts <= '2023-10-11 09:21:21'group by device_id