Clickhouse 分组查询排序取第一条数据

Clickhouse 分组查询排序取第一条数据

-- Note: Unless you save your query, these tabs will NOT persist if you clear your cookies or change browsers.

SELECT 
vin, 
vehicle_series_code,
vehicle_series_name,
vehicle_model_code,
vehicle_model_name,
battery_model,
--battery_type,
vehicle_sale_date,
battery_soc_start AS soc_start,
battery_soc_start_time AS charge_start_time,
battery_soc_end AS soc_end,
battery_soc_end_time AS charge_end_time,
total_mileage,
current_battery_capacity,
average_temp,
average_current,
current_battery_soh
FROM 
tdp_main.dwd_vehicle_data_charging_soh_u_d

WHERE 
data_validity_status = 1 and 
--vin = 'LFVVB9E67M5005361'
-- and battery_type = ''
--and vehicle_model_code like '%3C%'
-- and (total_mileage>=1.0 and total_mileage<=3.0 )
-- and (vehicle_sale_date>='2020-03-10' and vehicle_sale_date<='2020-05-10')
-- and (current_battery_soh>=70 and current_battery_soh <=80);

SELECT max(partition_key)

select vin,
       arrayJoin(groupArray(1)(vehicle_series_code)) AS vehicle_series_code,
    arrayJoin(groupArray(1)(vehicle_series_name)) AS vehicle_series_name,
    arrayJoin(groupArray(1)(vehicle_model_code)) AS vehicle_model_code,
    arrayJoin(groupArray(1)(vehicle_model_name)) AS vehicle_model_name,
    arrayJoin(groupArray(1)(battery_model)) AS battery_model,
    arrayJoin(groupArray(1)(battery_type)) AS battery_type,
    arrayJoin(groupArray(1)(vehicle_sale_date)) AS vehicle_sale_date,
    arrayJoin(groupArray(1)(battery_soc_start) ) AS soc_start,
    arrayJoin(groupArray(1)(battery_soc_start_time)  ) AS charge_start_time,
    arrayJoin(groupArray(1)(battery_soc_end)  ) AS soc_end,
    arrayJoin(groupArray(1)(battery_soc_end_time)  ) AS charge_end_time,
    arrayJoin(groupArray(1)(total_mileage)) AS total_mileage,
    arrayJoin(groupArray(1)(current_battery_capacity)) AS current_battery_capacity,
    arrayJoin(groupArray(1)(average_temp)) AS average_temp,
    arrayJoin(groupArray(1)(average_current)) AS average_current,
    arrayJoin(groupArray(1)(current_battery_soh)) AS current_battery_soh,
    arrayJoin(groupArray(1)(partition_key)) AS partition_key
from (select vin,partition_key,vehicle_series_code,
            vehicle_series_name,
            vehicle_model_code,
            vehicle_model_name,
            battery_model,
            battery_type,
            vehicle_sale_date,
            battery_soc_start ,
            battery_soc_start_time ,
            battery_soc_end ,
            battery_soc_end_time ,
            total_mileage,
            current_battery_capacity,
            average_temp,
            average_current,
            current_battery_soh
      from tdp_main.dwd_vehicle_data_charging_soh_u_d
      ORDER BY partition_key  desc
    ) a
group by vin