• Clickhouse 消除由group by产生的间隙

            这是好几个月前遇到的问题了，一直以为自己已经记录过了，突然发现并没有 /捂脸.jpg/。需求是根据日期、国家、供应商、设备……等条件，统计DUA、留存率等数据信息。这个需求不是本人负责，只是同事遇到由于group by而导致的日期不连续问题，问到我。本文仅提供一种思路而已，最终同事也并没有使用此方案在数据库层面解决此问题，而是在java程序中解决。

            1. 简化过的问题复现，e.g.由于中国区4月5到20日dua和use_times均为0，所以这几天在结果中消失了，产生了时间不连续。

    1. SELECT
    2.     event_date,
    3.     country_name,
    4.     groupBitmapOr(active_user) AS dau,
    5.     sum(use_times) AS use_times
    6. FROM dws_app_global_active_retained
    7. WHERE (country_name IN ('China', 'France', 'Hong Kong')) AND (event_date >= '2022-03-25')
    8. GROUP BY
    9.     event_date,
    10.     country_name
    11. ORDER BY
    12.     country_name ASC,
    13.     event_date ASC
    1. ┌─event_date─┬─country_name─┬─dau─┬─use_times─┐
    2. │ 2022-03-27 │ China        │   1 │         1 │
    3. │ 2022-03-28 │ China        │   1 │         2 │
    4. │ 2022-03-29 │ China        │   2 │        64 │
    5. │ 2022-03-30 │ China        │   1 │         4 │
    6. │ 2022-03-31 │ China        │   2 │         4 │
    7. │ 2022-04-01 │ China        │   1 │         5 │
    8. │ 2022-04-02 │ China        │   1 │         1 │
    9. │ 2022-04-03 │ China        │   1 │         1 │
    10. │ 2022-04-04 │ China        │   1 │         2 │
    11. │ 2022-04-21 │ China        │   1 │         5 │
    12. │ 2022-04-28 │ China        │   1 │         6 │
    13. │ 2022-04-29 │ China        │   1 │         4 │
    14. │ 2022-04-30 │ China        │   1 │        12 │
    15. │ 2022-05-01 │ China        │   1 │        10 │
    16. │ 2022-05-02 │ China        │   1 │         9 │
    17. │ 2022-05-03 │ China        │   1 │         3 │
    18. │ 2022-05-04 │ China        │   1 │         2 │
    19. │ 2022-05-05 │ China        │   1 │         6 │
    20. │ 2022-05-06 │ China        │   1 │         3 │
    21. │ 2022-05-07 │ China        │   1 │         5 │
    22. │ 2022-05-08 │ China        │   1 │         4 │
    23. │ 2022-03-25 │ Hong Kong    │   2 │         5 │
    24. │ 2022-03-26 │ Hong Kong    │   1 │         2 │
    25. │ 2022-03-27 │ Hong Kong    │   1 │         2 │
    26. │ 2022-03-28 │ Hong Kong    │   2 │        26 │
    27. │ 2022-03-29 │ Hong Kong    │   2 │        56 │
    28. │ 2022-03-30 │ Hong Kong    │   3 │        17 │
    29. │ 2022-03-31 │ Hong Kong    │   4 │        12 │
    30. │ 2022-04-01 │ Hong Kong    │   1 │         1 │
    31. │ 2022-04-02 │ Hong Kong    │   2 │        23 │
    32. │ 2022-04-06 │ Hong Kong    │   1 │         1 │
    33. │ 2022-04-07 │ Hong Kong    │   2 │         9 │
    34. │ 2022-04-08 │ Hong Kong    │   2 │        29 │
    35. └────────────┴──────────────┴─────┴───────────┘

            2. 先填补时间间隙（WITH FILL STEP n）

    1. SELECT
    2.     event_date,
    3.     country_name,
    4.     groupBitmapOr(active_user) AS dau,
    5.     sum(use_times) AS use_times
    6. FROM dws_app_global_active_retained
    7. WHERE (country_name IN ('China', 'France', 'Hong Kong')) AND (event_date >= '2022-03-25')
    8. GROUP BY
    9.     event_date,
    10.     country_name
    11. ORDER BY
    12.     country_name ASC,
    13.     event_date ASC WITH FILL STEP 1
    1.  
    2. ┌─event_date─┬─country_name─┬─dau─┬─use_times─┐
    3. │ 2022-03-27 │ China        │   1 │         1 │
    4. │ 2022-03-28 │ China        │   1 │         2 │
    5. │ 2022-03-29 │ China        │   2 │        64 │
    6. │ 2022-03-30 │ China        │   1 │         4 │
    7. │ 2022-03-31 │ China        │   2 │         4 │
    8. │ 2022-04-01 │ China        │   1 │         5 │
    9. │ 2022-04-02 │ China        │   1 │         1 │
    10. │ 2022-04-03 │ China        │   1 │         1 │
    11. │ 2022-04-04 │ China        │   1 │         2 │
    12. │ 2022-04-05 │              │   0 │         0 │
    13. │ 2022-04-06 │              │   0 │         0 │
    14. │ 2022-04-07 │              │   0 │         0 │
    15. │ 2022-04-08 │              │   0 │         0 │
    16. │ 2022-04-09 │              │   0 │         0 │
    17. │ 2022-04-10 │              │   0 │         0 │
    18. │ 2022-04-11 │              │   0 │         0 │
    19. │ 2022-04-12 │              │   0 │         0 │
    20. │ 2022-04-13 │              │   0 │         0 │
    21. │ 2022-04-14 │              │   0 │         0 │
    22. │ 2022-04-15 │              │   0 │         0 │
    23. │ 2022-04-16 │              │   0 │         0 │
    24. │ 2022-04-17 │              │   0 │         0 │
    25. │ 2022-04-18 │              │   0 │         0 │
    26. │ 2022-04-19 │              │   0 │         0 │
    27. │ 2022-04-20 │              │   0 │         0 │
    28. │ 2022-04-21 │ China        │   1 │         5 │
    29. │ 2022-04-22 │              │   0 │         0 │
    30. │ 2022-04-23 │              │   0 │         0 │
    31. │ 2022-04-24 │              │   0 │         0 │
    32. │ 2022-04-25 │              │   0 │         0 │
    33. │ 2022-04-26 │              │   0 │         0 │
    34. │ 2022-04-27 │              │   0 │         0 │
    35. │ 2022-04-28 │ China        │   1 │         6 │
    36. │ 2022-04-29 │ China        │   1 │         4 │
    37. │ 2022-04-30 │ China        │   1 │        12 │
    38. │ 2022-05-01 │ China        │   1 │        10 │
    39. │ 2022-05-02 │ China        │   1 │         9 │
    40. │ 2022-05-03 │ China        │   1 │         3 │
    41. │ 2022-05-04 │ China        │   1 │         2 │
    42. │ 2022-05-05 │ China        │   1 │         6 │
    43. │ 2022-05-06 │ China        │   1 │         3 │
    44. │ 2022-05-07 │ China        │   1 │         5 │
    45. │ 2022-05-08 │ China        │   1 │         4 │
    46. │ 2022-03-25 │ Hong Kong    │   2 │         5 │
    47. │ 2022-03-26 │ Hong Kong    │   1 │         2 │
    48. │ 2022-03-27 │ Hong Kong    │   1 │         2 │
    49. │ 2022-03-28 │ Hong Kong    │   2 │        26 │
    50. │ 2022-03-29 │ Hong Kong    │   2 │        56 │
    51. │ 2022-03-30 │ Hong Kong    │   3 │        17 │
    52. │ 2022-03-31 │ Hong Kong    │   4 │        12 │
    53. │ 2022-04-01 │ Hong Kong    │   1 │         1 │
    54. │ 2022-04-02 │ Hong Kong    │   2 │        23 │
    55. │ 2022-04-06 │ Hong Kong    │   1 │         1 │
    56. │ 2022-04-07 │ Hong Kong    │   2 │         9 │
    57. │ 2022-04-08 │ Hong Kong    │   2 │        29 │
    58. └────────────┴──────────────┴─────┴───────────┘

            3. 此时又出现了新的问题，即country_name产生了间隙，所以需要利用之前《Clickhouse 空缺值处理》这篇文章提到的“填充相邻值法”对间隙进行填充（arrayFill）。

    1. with temp as( SELECT
    2.     event_date,
    3.     country_name,
    4.     groupBitmapOr(active_user) AS dau,
    5.     sum(use_times) AS use_times
    6. FROM dws_app_global_active_retained
    7. WHERE (country_name IN ('China', 'France', 'Hong Kong')) AND (event_date >= '2022-03-25')
    8. GROUP BY
    9.     event_date,
    10.     country_name
    11. ORDER BY
    12.     country_name ASC,
    13.     event_date ASC WITH FILL STEP 1 )
    14.  
    15. select tuple.1 as event_date, tuple.2 as country_name,tuple.3 as dau, tuple.4 as use_times from
    16. (select  arrayJoin(
    17. 		arrayZip(
    18. 			groupArray(event_date),
    19. 			arrayFill(x ->x !='',groupArray(country_name)) ,
    20. 			groupArray(dau),
    21. 			groupArray(use_times) 
    22. 		)
    23. 	) as tuple
    24. from temp)
    1. ┌─event_date─┬─country_name─┬─dau─┬─use_times─┐
    2. │ 2022-03-27 │ China        │   1 │         1 │
    3. │ 2022-03-28 │ China        │   1 │         2 │
    4. │ 2022-03-29 │ China        │   2 │        64 │
    5. │ 2022-03-30 │ China        │   1 │         4 │
    6. │ 2022-03-31 │ China        │   2 │         4 │
    7. │ 2022-04-01 │ China        │   1 │         5 │
    8. │ 2022-04-02 │ China        │   1 │         1 │
    9. │ 2022-04-03 │ China        │   1 │         1 │
    10. │ 2022-04-04 │ China        │   1 │         2 │
    11. │ 2022-04-05 │ China        │   0 │         0 │
    12. │ 2022-04-06 │ China        │   0 │         0 │
    13. │ 2022-04-07 │ China        │   0 │         0 │
    14. │ 2022-04-08 │ China        │   0 │         0 │
    15. │ 2022-04-09 │ China        │   0 │         0 │
    16. │ 2022-04-10 │ China        │   0 │         0 │
    17. │ 2022-04-11 │ China        │   0 │         0 │
    18. │ 2022-04-12 │ China        │   0 │         0 │
    19. │ 2022-04-13 │ China        │   0 │         0 │
    20. │ 2022-04-14 │ China        │   0 │         0 │
    21. │ 2022-04-15 │ China        │   0 │         0 │
    22. │ 2022-04-16 │ China        │   0 │         0 │
    23. │ 2022-04-17 │ China        │   0 │         0 │
    24. │ 2022-04-18 │ China        │   0 │         0 │
    25. │ 2022-04-19 │ China        │   0 │         0 │
    26. │ 2022-04-20 │ China        │   0 │         0 │
    27. │ 2022-04-21 │ China        │   1 │         5 │
    28. │ 2022-04-22 │ China        │   0 │         0 │
    29. │ 2022-04-23 │ China        │   0 │         0 │
    30. │ 2022-04-24 │ China        │   0 │         0 │
    31. │ 2022-04-25 │ China        │   0 │         0 │
    32. │ 2022-04-26 │ China        │   0 │         0 │
    33. │ 2022-04-27 │ China        │   0 │         0 │
    34. │ 2022-04-28 │ China        │   1 │         6 │
    35. │ 2022-04-29 │ China        │   1 │         4 │
    36. │ 2022-04-30 │ China        │   1 │        12 │
    37. │ 2022-05-01 │ China        │   1 │        10 │
    38. │ 2022-05-02 │ China        │   1 │         9 │
    39. │ 2022-05-03 │ China        │   1 │         3 │
    40. │ 2022-05-04 │ China        │   1 │         2 │
    41. │ 2022-05-05 │ China        │   1 │         6 │
    42. │ 2022-05-06 │ China        │   1 │         3 │
    43. │ 2022-05-07 │ China        │   1 │         5 │
    44. │ 2022-05-08 │ China        │   1 │         4 │
    45. │ 2022-03-25 │ Hong Kong    │   2 │         5 │
    46. │ 2022-03-26 │ Hong Kong    │   1 │         2 │
    47. │ 2022-03-27 │ Hong Kong    │   1 │         2 │
    48. │ 2022-03-28 │ Hong Kong    │   2 │        26 │
    49. │ 2022-03-29 │ Hong Kong    │   2 │        56 │
    50. │ 2022-03-30 │ Hong Kong    │   3 │        17 │
    51. │ 2022-03-31 │ Hong Kong    │   4 │        12 │
    52. │ 2022-04-01 │ Hong Kong    │   1 │         1 │
    53. │ 2022-04-02 │ Hong Kong    │   2 │        23 │
    54. │ 2022-04-06 │ Hong Kong    │   1 │         1 │
    55. │ 2022-04-07 │ Hong Kong    │   2 │         9 │
    56. │ 2022-04-08 │ Hong Kong    │   2 │        29 │
    57. └────────────┴──────────────┴─────┴───────────┘

  • 相关阅读:
    代码自动化审核操作详解(svnchecker+checkstyle)
    Fury：一个基于JIT动态编译的高性能多语言原生序列化框架
    【中秋国庆不断更】OpenHarmony定义扩展组件样式：@Extend装饰器
    官网下载JAVA的JDK11版本（下载、安装、配置环境变量）
    HR人才测评，什么是全局观念？如何测评全局观念？
    springboot导入excel（POI）
    自然语言处理中的文本聚类：揭示模式和见解
    如何PDF转Word文档？两分钟教你三个方法
    程序员都应该懂Kubernetes的基本概念和关键组件是什么吗？
    Html学习
  • 原文地址：https://blog.csdn.net/m0_37795099/article/details/125505780