• clickhouse

    官方链接

    1. <insert id="insertTable" parameterType="com.ioc.orm.ck.model.TableModel">
    2.     insert into table_name
    3.     <trim prefix="(" suffix=")" suffixOverrides=",">
    4.         <if test="ts != null">
    5.             ts,
    6.         if>
    7.         <if test="deviceId != null">
    8.             device_id,
    9.         if>
    10.         <if test="driverId != null">
    11.             driver_id,
    12.         if>
    13.         <if test="longitude != null">
    14.             longitude,
    15.         if>
    16.         <if test="latitude != null">
    17.             latitude,
    18.         if>
    19.         <if test="altitude != null">
    20.             altitude,
    21.         if>
    22.         <if test="direction != null">
    23.             direction,
    24.         if>
    25.         <if test="mileage != null">
    26.             mileage,
    27.         if>
    28.         <if test="rssi != null">
    29.             rssi,
    30.         if>
    31.         <if test="satellites != null">
    32.             satellites,
    33.         if>
    34.         <if test="alarm != null">
    35.             alarm,
    36.         if>
    37.         <if test="signalStatus != null">
    38.             signal_status,
    39.         if>
    40.         <if test="gmtCreate != null">
    41.             gmt_create,
    42.         if>
    43.         <if test="kind != null">
    44.             kind,
    45.         if>
    46.         <if test="oil != null">
    47.             oil,
    48.         if>
    49.         <if test="plate != null and plate!=''">
    50.             plate,
    51.         if>
    52.         <if test="deviceGroupId != null">
    53.             device_group_id,
    54.         if>
    55.         <if test="acc != null">
    56.             acc,
    57.         if>
    58.         <if test="status != null">
    59.             status,
    60.         if>
    61.         <if test="mobile != null and mobile!=''">
    62.             mobile,
    63.         if>
    64.         <if test="provinceId != null">
    65.             province_id,
    66.         if>
    67.         <if test="cityId != null">
    68.             city_id,
    69.         if>
    70.         <if test="districtId != null">
    71.             district_id,
    72.         if>
    73.         <if test="address != null and address!=''">
    74.             address,
    75.         if>
    76.         <if test="bdLongitude != null">
    77.             bd_longitude,
    78.         if>
    79.         <if test="bdLatitude != null">
    80.             bd_latitude,
    81.         if>
    82.         <if test="gdLongitude != null">
    83.             gd_longitude,
    84.         if>
    85.         <if test="gdLatitude != null">
    86.             gd_latitude,
    87.         if>
    88.         <if test="speed != null">
    89.             speed,
    90.         if>
    91.         <if test="speed2 != null">
    92.             speed2,
    93.         if>
    94.         <if test="mileage2 != null">
    95.             mileage2
    96.         if>
    97.     trim>
    98.     <trim prefix="values (" suffix=")" suffixOverrides=",">
    99.         <if test="ts != null">
    100.             #{ts},
    101.         if>
    102.         <if test="deviceId != null">
    103.             #{deviceId},
    104.         if>
    105.         <if test="driverId != null">
    106.             #{driverId},
    107.         if>
    108.         <if test="longitude != null">
    109.             #{longitude},
    110.         if>
    111.         <if test="latitude != null">
    112.             #{latitude},
    113.         if>
    114.         <if test="altitude != null">
    115.             #{altitude},
    116.         if>
    117.         <if test="direction != null">
    118.             #{direction},
    119.         if>
    120.         <if test="mileage != null">
    121.             #{mileage},
    122.         if>
    123.         <if test="rssi != null">
    124.             #{rssi},
    125.         if>
    126.         <if test="satellites != null">
    127.             #{satellites},
    128.         if>
    129.         <if test="alarm != null">
    130.             #{alarm},
    131.         if>
    132.         <if test="signalStatus != null">
    133.             #{signalStatus},
    134.         if>
    135.         <if test="gmtCreate != null">
    136.             #{gmtCreate},
    137.         if>
    138.         <if test="kind != null">
    139.             #{kind},
    140.         if>
    141.         <if test="oil != null">
    142.             #{oil},
    143.         if>
    144.         <if test="plate != null and plate!=''">
    145.             #{plate},
    146.         if>
    147.         <if test="deviceGroupId != null">
    148.             #{deviceGroupId},
    149.         if>
    150.         <if test="acc != null">
    151.             #{acc},
    152.         if>
    153.         <if test="status != null">
    154.             #{status},
    155.         if>
    156.         <if test="mobile != null and mobile!=''">
    157.             #{mobile},
    158.         if>
    159.         <if test="provinceId != null">
    160.             #{provinceId},
    161.         if>
    162.         <if test="cityId != null">
    163.             #{cityId},
    164.         if>
    165.         <if test="districtId != null">
    166.             #{districtId},
    167.         if>
    168.         <if test="address != null and address!=''">
    169.             #{address},
    170.         if>
    171.         <if test="bdLongitude != null">
    172.             #{bdLongitude},
    173.         if>
    174.         <if test="bdLatitude != null">
    175.             #{bdLatitude},
    176.         if>
    177.         <if test="gdLongitude != null">
    178.             #{gdLongitude},
    179.         if>
    180.         <if test="gdLatitude != null">
    181.             #{gdLatitude},
    182.         if>
    183.         <if test="speed != null">
    184.             #{speed},
    185.         if>
    186.         <if test="speed2 != null">
    187.             #{speed2},
    188.         if>
    189.         <if test="mileage2 != null">
    190.             #{mileage2}
    191.         if>
    192.     trim>
    1. --导出CSV，无权限？
    2.  
    3. SELECT *
    4.  
    5. FROM table_name
    6.  
    7. INTO OUTFILE 'out.csv'
    8.  
    9. FORMAT CSVWithNames
    10.  
    11.  
    12.  
    13. --插入车辆轨迹
    14.  
    15. 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);
    16.  
    17. 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);
    18.  
    19. 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);
    20.  
    21. 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);
    22.  
    23. 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);
    24.  
    25. 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);
    26.  
    27. 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);
    28.  
    29. 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);
    30.  
    31. 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);
    32.  
    33. 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);
    34.  
    35. --车辆最新轨迹点，9s
    36.  
    37. SELECT distinct *
    38.  
    39. FROM table_name
    40.  
    41. WHERE (device_id , ts) IN (
    42.  
    43. SELECT
    44.  
    45. device_id,
    46.  
    47. max(ts) as ts
    48.  
    49. FROM table_name
    50.  
    51. GROUP BY device_id
    52.  
    53. )
    54.  
    55. SELECT *
    56.  
    57. FROM table_name
    58.  
    59. WHERE (device_id , ts) IN (
    60.  
    61. SELECT
    62.  
    63. device_id,
    64.  
    65. max(ts) as ts
    66.  
    67. FROM table_name
    68.  
    69. GROUP BY device_id
    70.  
    71. )
    72.  
    73. --车辆最新轨迹点，24s
    74.  
    75. SELECT * FROM
    76.  
    77. (
    78.  
    79. SELECT
    80.  
    81. device_id,
    82.  
    83. max(ts) AS ts
    84.  
    85. FROM table_name
    86.  
    87. GROUP BY device_id
    88.  
    89. ) a
    90.  
    91. INNER JOIN table_name b ON a.ts=b.ts AND a.device_id=b.device_id
    92.  
    93.  
    94.  
    95. --3天以内车速为0的轨迹点，15s
    96.  
    97. SELECT *
    98.  
    99. FROM table_name
    100.  
    101. WHERE device_id = 5540465
    102.  
    103. AND ts >= '2023-10-09 19:00:00'
    104.  
    105. AND ts <= '2023-10-11 09:21:21'
    106.  
    107. AND speed=0
    108.  
    109. AND speed2=0
    110.  
    111. ORDER BY ts
    112.  
    113. --历史轨迹，10s
    114.  
    115. select *
    116.  
    117. from table_name
    118.  
    119. where device_id = 5540465
    120.  
    121. AND ts >= '2023-10-09 19:00:00'
    122.  
    123. AND ts <= '2023-10-11 09:21:21'
    124.  
    125. --轨迹点数量，6s
    126.  
    127. select count(1) as total
    128.  
    129. from table_name
    130.  
    131. where device_id = 5540465
    132.  
    133. AND ts >= '2023-10-09 19:00:00'
    134.  
    135. AND ts <= '2023-10-11 09:21:21'
    136.  
    137.  
    138.  
    139. --车辆小时里程统计，1s
    140.  
    141. SELECT device_id as deviceId,
    142.  
    143. (last(mileage) - first(mileage)) as mileage,
    144.  
    145. (last(mileage2) - first(mileage2)) as mileage2,
    146.  
    147. first(mileage) firstMile,
    148.  
    149. last(mileage) lastMile,
    150.  
    151. first(mileage2) firstMile2,
    152.  
    153. last(mileage2) lastMile2
    154.  
    155. FROM table_name
    156.  
    157. where ts >= '2023-10-09 19:00:00'
    158.  
    159. AND ts <= '2023-10-11 09:21:21'
    160.  
    161. AND mileage > 0
    162.  
    163. group by device_id
    164.  
    165.  
    166.  
    167. SELECT device_id as deviceId,
    168.  
    169. first_value(mileage) firstMile,
    170.  
    171. last_value(mileage) lastMile,
    172.  
    173. first_value(mileage2) firstMile2,
    174.  
    175. last_value(mileage2) lastMile2
    176.  
    177. FROM table_name
    178.  
    179. where ts >= '2023-10-09 19:00:00'
    180.  
    181. AND ts <= '2023-10-11 09:21:21'
    182.  
    183. group by device_id

  • 相关阅读:
    利用js和audio标签读取音频文件并计算音频时长
    linux查找目录
    操作系统复习【面试】
    Vue3 + Nodejs 实战 ，文件上传项目--实现拖拽上传
    SQL Server数据库语法篇（终篇）
    Elasticsearch-- cat & cluster 命令使用
    css 控制鼠标不能点击
    treevalue——Master Nested Data Like Tensor
    ROS2架构分析总结（一）
    求最大公约数和最小公倍数
  • 原文地址：https://blog.csdn.net/gogler/article/details/133926427