geedge-jira/md/OMPUB-417.md

# 新疆联通报告生成错误

| ID | Creation Date | Assignee | Status |
|----|----------------|----------|--------|
| OMPUB-417 | 2022-03-16T12:52:07.000+0800 | 戚岱杰 | 已关闭 |


---

{{按照数据集-图表库-报告结果的流程生成一份Overseas APP server IP和location的相关报告，名为whatsapp的报告无法生成，具体表现为：创建报告后，生成进度持续时间{*}非常久{*}，并且当进度达到90%左右突然跳到60%左右，一直如此循环，最终，进度显示在98%，报告生成失败。}}

{{具体{*}操作步骤{*}如下：}}

{{1.建立如图1-1所示名为Whatsapp Server IP and Location的数据集；}}

{{2.再建立如图1-2所示名为Whatsapp TOP 10 Server IP and Location的图表库，并引用图1-1中的数据集;}}

{{3.最后生成图1-3所示的报告结果;}}

{{4.点击生成报告后，抓取了四次，按照时间顺序依次排列如图1-4所示，生成进度到90%之后又跳回68%，再次到94%又跳回81%，一直循环，最后报告会停止在进度98%的时候，显示生成报告失败;}}

{{5.在此处，F12查看返回数据如图1-5所示，显示status=4，数据量也异常大，达到几百亿。}}**qidaijie** commented on *2022-03-17T13:49:59.099+0800*:

1.此报告进度条出现循环的原因：因为union all加子查询造成进度条循环，ck需要将sql解析分为多段进行查询。此现象不影响最终查询结果。
2.此报告查询的为xj联通一周的数据，数据量为3000亿，union all之后相同的查询执行了两次数据量达到6000亿，数据量较大。
3.clickhouse解析执行union all查询的性能远低于分别进行多次查询的性能。

综合以上原因此查询超时
目前报告生成的SQL [^报告原始sql] ；对此查询进行优化，优化后sql如下：
{code:sql}
SELECT
        CAST("Server Location" AS varchar) AS "Server Location",
        sum("Bytes Sent + Bytes Received") AS "Bytes Sent + Bytes Received"
FROM
        (
        SELECT
                common_server_location AS "Server Location", common_server_ip AS "Server IP", sum(common_c2s_pkt_num + common_s2c_pkt_num) AS "Packets Sent + Packets Received", sum(common_c2s_byte_num + common_s2c_byte_num) AS "Bytes Sent + Bytes Received"
        FROM
                tsg_galaxy_v3.session_record AS session_record
        WHERE
                ((common_recv_time >= toDateTime('2022-03-06 00:00:00')
                AND common_recv_time < toDateTime('2022-03-13 00:00:00')))
                AND ((common_app_label = 'whatsapp'))
        GROUP BY
                "Server Location", "Server IP")
GROUP BY
        "Server Location" WITH ROLLUP
ORDER BY
        "Bytes Sent + Bytes Received" DESC
LIMIT 10  
{code}
使用{color:#FF0000}WITH ROLLUP{color} 也可以计算出total值，{*}计算时间缩短{*} 到{color:#FF0000}1000s{color}

此优化函数仅针对于clickhouse数据库使用，优化需深入讨论；目前建议缩短报表查询时间，分段查询。



---



# Attachments

Attachment: 报告原始sql

[报告原始sql](https://gfwleak.exec.li/admin/geedge-jira/raw/branch/master/attachment/26282/报告原始sql)



Attachment: 图1-1+数据集页面.png

![图1-1+数据集页面.png](https://gfwleak.exec.li/admin/geedge-jira/raw/branch/master/attachment/26230/图1-1+数据集页面.png)



Attachment: 图1-2图表库页面.png

![图1-2图表库页面.png](https://gfwleak.exec.li/admin/geedge-jira/raw/branch/master/attachment/26229/图1-2图表库页面.png)



Attachment: 图1-3生成报告页面.png

![图1-3生成报告页面.png](https://gfwleak.exec.li/admin/geedge-jira/raw/branch/master/attachment/26228/图1-3生成报告页面.png)



Attachment: 图1-4生成进度详情.png

![图1-4生成进度详情.png](https://gfwleak.exec.li/admin/geedge-jira/raw/branch/master/attachment/26227/图1-4生成进度详情.png)



Attachment: 图1-5+返回数据详情.png

![图1-5+返回数据详情.png](https://gfwleak.exec.li/admin/geedge-jira/raw/branch/master/attachment/26226/图1-5+返回数据详情.png)