clickhouse

攻城无数

已于 2023-10-19 15:08:11 修改

阅读量729

点赞数

文章标签： clickhouse

于 2023-10-19 15:02:16 首次发布

本文链接：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