如何按天获取参数值？

nvbavucw 于 2021-07-15 发布在 ClickHouse

关注(0)|答案(1)|浏览(373)

我需要找到每天排名中每个玩家的属性值。
我有下表：

player_id | date     | level
----------------------------
   pl1    |2018-01-01|   3
   pl1    |2018-01-02|   3
   pl1    |2018-01-03|   4
   pl1    |2018-01-05|   4
   pl1    |2018-01-06|   4
   pl1    |2018-01-08|   5
   pl2    |2018-01-05|   1

我需要得到下一个结果：

player_id | level_by_date
-----------------------------
   pl1    | (3,3,4,4,4,4,4,5)
   pl2    | (0,0,0,0,1,1,1,1)

我试着用另一种方法做，但失败了

SELECT
 player_id,
 groupArray(max(module_level))
FROM d_Modules
WHERE 
 date>='2018-01-01' AND date<=arrayMap(i -> (toDate('2018-12-31') + toIntervalDay(i)), range(toUInt64((toDate('2018-12-31') - toDate('2018-01-01')) + 1)))

我该怎么做？

clickhouse

来源：https://stackoverflow.com/questions/54128519/how-to-get-the-parameter-value-by-day

1条答案

按热度按时间

w1jd8yoj1#

查询可以如下所示：

SELECT
    player_id,
    arrayMap(date -> (date, 0), arrayFilter(date -> (arrayExists(i -> (i.1 = date), origin_level_by_date) = 0), dateRange)) AS missed_level_by_date,
    arraySort(x -> x.1, arrayConcat(origin_level_by_date, missed_level_by_date)) AS level_by_date,
    arrayMap(x -> x.2, level_by_date) AS result
FROM
(
    SELECT
        player_id,
        groupArray((toDate(date), level)) AS origin_level_by_date
    FROM d_Modules
    WHERE (date >= '2018-01-01 00:00:00') AND (date < '2019-01-01 00:00:00')
    GROUP BY player_id
)
CROSS JOIN
(
    SELECT
        min(toDate(date)) AS min,
        max(toDate(date)) AS max,
        arrayMap(i -> (min + i), range(toUInt32((max - min) + 1))) AS dateRange
    FROM d_Modules
    WHERE (date >= '2018-01-01 00:00:00') AND (date < '2019-01-01 00:00:00')
)
ORDER BY player_id ASC
FORMAT Vertical

结果与要求稍有不同，因为对于错过的天数，级别值为零：

┌─player_id─┬─result────────────┐
│ pl1       │ [3,3,4,0,4,4,0,5] │
│ pl2       │ [0,0,0,0,1,0,0,0] │
└───────────┴───────────────────┘

赞(0）回复(0）举报 2021-07-15

我来回答

如何按天获取参数值？

1条答案

相关问题

热门标签

最新问答