如何转换Grafana图形所能理解的点击日期？

Question

我的查询如下：

SELECT
  (date),
  CVC_Demand_Per_Subscriber
FROM
  (SELECT
    date,
    sum(Max_Utilization) as SUM_Max_Util,
    sum(AVC) as SUM_Total_Active_AVCs,
    (SUM_Max_Util/SUM_Total_Active_AVCs) as CVC_Demand_Per_Subscriber
  FROM
  (
        SELECT
            date,
            cvc as CVC,
            avc as AVC,
            bandwidth,
            round((max(lout)/1000000),2) as Max_Utilization,
            ((((max(lout) / bandwidth) * 100)) / 1000000) as Max_Utilization_Percent,
            (Max_Utilization/AVC) as CVC_Demand_Per_Subscriber
        FROM
        (
            SELECT
                date,
                path[2] as cvc,
                bandwidth,
                avc,
                max(load_out) as lout
            FROM noc.interface    
        ANY INNER JOIN
        (
                SELECT
                    cvcid as cvc,
                    bandwidth,
                    activeavc as avc
                FROM dictionaries.nsi_cvcs
                GROUP BY
                    cvc,
                    avc,
                    bandwidth
        ) USING  cvc
            WHERE
                managed_object IN (
            SELECT bi_id
            FROM dictionaries.managedobject
            WHERE nbn = 1)
                AND(date >= today()- 7)
            GROUP BY
                date,
                cvc,
                avc,
                bandwidth
            ORDER BY
                date,
                cvc,
                avc
        )
        GROUP BY
            date,
            cvc,
            avc,
            bandwidth
  ) 
  GROUP BY date
  ORDER BY date ASC)
  tmp 

在Grafana中选择Table时，我将得到结果数据，如下所示：

Time                CVC_Demand_Per_Subscriber
2021-07-19 00:00:00 1.61
2021-07-18 00:00:00 2.70
2021-07-17 00:00:00 2.90
2021-07-16 00:00:00 2.83
2021-07-15 00:00:00 2.54
2021-07-14 00:00:00 2.38
2021-07-13 00:00:00 2.39
2021-07-12 00:00:00 0.64

但是，当我将它更改为图时，我没有看到根据日期绘制的与值一起绘制的图形。它不是说“没有数据”，而是一个空图。

请帮我找错地方？

我试过以下几种方法，但没有运气：

1. 用UNIX_TIMESTAMP转换日期
2. to_char(date_format)
3. $__timeGroup()
4. $__time

还请建议对查询进行优化。

​

​

Answer 1

它需要：

• 定义列:日期时间作为时间

​

​

• 设置sql-查询。

SELECT
    $timeSeries as t,
    sum(CVC_Demand_Per_Subscriber) value
FROM  (
  /* emulate the test dataset */
  SELECT toDateTime(data.1) AS Time, data.2 AS CVC_Demand_Per_Subscriber
  FROM (
    SELECT arrayJoin([
      ('2021-07-19 00:00:00', 1.61),
      ('2021-07-18 00:00:00', 2.70),
      ('2021-07-17 00:00:00', 2.90),
      ('2021-07-16 00:00:00', 2.83),
      ('2021-07-15 00:00:00', 2.54),
      ('2021-07-14 00:00:00', 2.38),
      ('2021-07-13 00:00:00', 2.39),
      ('2021-07-12 00:00:00', 0.64)]) as data)
  )
WHERE $timeFilter
GROUP BY t
ORDER BY t

​

​

当图形为空(显示“无数据”)且没有查询错误时，需要检查ClickHouse数据资源集以确保向请求中添加CORS标志，被启用：

​

​