基本上,我的表具有以下数据结构:
id_level1
:Int32id_level2
:Int32event_date
:日期arr_object_ids
:Int32数组 -按下一列排序arr_object_dates
:日期数组 -升序排列我需要的是每对具有至少object_date
一个以上event_date
的值(id_leve1, id_level2)
。在Clickhouse中怎么可能?
然后我会arrayElement(arr_object_ids, indexOf(arr_object_dates, solution)
用来获取对应的object_id
试试这个查询:
SELECT
id_level1,
id_level2,
/*arrayFirst(x -> x > event_date, arr_object_dates) least_date,*/
arrayFirstIndex(x -> x > event_date, arr_object_dates) least_date_index,
least_date_index = 0 ? -1 : arrayElement(arr_object_ids, least_date_index) object_id /* -1 if result not found */
FROM (
/* emulate original table */
SELECT 1 id_level1, 2 id_level2, '2020-01-03' event_date,
[4, 5, 6,7] arr_object_ids,
['2020-01-01', '2020-01-03', '2020-01-06', '2020-01-11'] arr_object_dates
UNION ALL
SELECT 3 id_level1, 4 id_level2, '2020-05-03' event_date,
[4, 5, 6,7] arr_object_ids,
['2020-01-01', '2020-01-03', '2020-01-06', '2020-01-11'] arr_object_dates)
ORDER BY event_date
/* result
┌─id_level1─┬─id_level2─┬─least_date_index─┬─object_id─┐
│ 1 │ 2 │ 3 │ 6 │
│ 3 │ 4 │ 0 │ -1 │
└───────────┴───────────┴──────────────────┴───────────┘
*/
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句