SuperSet 查询Druid.io 时区转换查询

最新推荐文章于 2024-03-12 18:48:29 发布

dinghua_xuexi

最新推荐文章于 2024-03-12 18:48:29 发布

阅读量491

点赞数

CC 4.0 BY-SA版权

分类专栏：大数据 Druid.io 文章标签：大数据 sql

本文链接：https://blog.csdn.net/dinghua_xuexi/article/details/123865248

大数据同时被 2 个专栏收录

26 篇文章

订阅专栏

Druid.io

4 篇文章

订阅专栏

本文介绍了一种使用SQL从游戏房间信息表中查询特定时间段内各房间的游戏类型、模式、首局及末局时间与总场数的方法。通过精确的时间戳处理确保了数据的准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT FLOOR(TIMESTAMPADD(HOUR, 8, __time) TO DAY) AS "__timestamp",
       "room_id" AS "room_id",
       "game_type" AS "game_type",
       "game_mode" AS "game_mode",
       TIME_FORMAT(MILLIS_TO_TIMESTAMP(MIN(start_time_min)*1000), 'yyyy-MM-dd HH:mm:ss', 'Asia/Shanghai') AS "首局开始时间",
       TIME_FORMAT(MILLIS_TO_TIMESTAMP(MAX(end_time_max)*1000), 'yyyy-MM-dd HH:mm:ss', 'Asia/Shanghai') AS "末局结束时间",
       SUM(row_count) AS "场数"
FROM "druid"."aw_game_room_info"
WHERE TIMESTAMPADD(HOUR, 8, __time) >= TIME_PARSE('2022-03-24T00:00:00')
  AND TIMESTAMPADD(HOUR, 8, __time) < TIME_PARSE('2022-03-31T00:00:00')
GROUP BY "room_id",
         "game_type",
         "game_mode",
         FLOOR(TIMESTAMPADD(HOUR, 8, __time) TO DAY)
ORDER BY "首局开始时间" DESC
LIMIT 10000;