时间戳

首页 > 解决方案 > 同一 cloumn 上两个值的百分比差异

sql - 同一 cloumn 上两个值的百分比差异

问题描述

我想从 avg 列中获取两个值之间的百分比差异，以作为方差显示在不同的列中。以下示例是虚拟表，因为表中的数据很大。值不一定相同。

每个资源 id 的第一个值是当前月份的值，而另一个是之前所有月份的平均值。此结果是从两个查询的“union all”中获得的。我想计算每个资源 id 的平均值的百分比增加/减少

桌子

resource_id avg
----------- -------
101         20
101         15
102         18
102         20

我需要的结果

resource_id variance
----------- ---------
101         25
102        -10

我从中获取表格的原始查询。

SELECT resource_id,
       avg
FROM   (SELECT resource_id,
               Avg(sum_cost) AS AVG
        FROM  (SELECT resource_id,
                      month,
                      Sum(sum_cost)AS SUM_COST
               FROM   (SELECT billing_bigquery_data.labels_value         AS
                              resource_id
                              ,
                              Extract(month FROM Date(
                                      usage_start_time)) AS MONTH,
                              Sum(cost)                                  AS
                              sum_cost
                       FROM   PUBLIC.billing_bigquery_data
                              INNER JOIN PUBLIC.conf_data_history
                                      ON billing_bigquery_data.labels_value =
                                         conf_data_history.resource_id
                       WHERE  conf_data_history.metric_name IN( 'VOLUME_TYPE' )
                              AND labels_key IN( 'disk_id', 'instance_id' )
                              AND Extract(month FROM Date(usage_start_time)) !=
                                  '12'
                       GROUP  BY conf_data_history.metric_value,
                                 billing_bigquery_data.labels_value,
                                 billing_bigquery_data.usage_start_time)AS test
               GROUP  BY month,
                         resource_id
               ORDER  BY resource_id) AS test
        GROUP  BY resource_id
        UNION ALL
        SELECT resource_id,
               Avg(sum_cost) AS AVG
        FROM  (SELECT resource_id,
                      month,
                      Sum(sum_cost)AS SUM_COST
               FROM   (SELECT billing_bigquery_data.labels_value         AS
                              resource_id
                              ,
                              Extract(month FROM Date(
                                      usage_start_time)) AS MONTH,
                              Sum(cost)                                  AS
                              sum_cost
                       FROM   PUBLIC.billing_bigquery_data
                              INNER JOIN PUBLIC.conf_data_history
                                      ON billing_bigquery_data.labels_value =
                                         conf_data_history.resource_id
                       WHERE  conf_data_history.metric_name IN( 'VOLUME_TYPE' )
                              AND labels_key IN( 'disk_id', 'instance_id' )
                              AND Extract(month FROM Date(usage_start_time)) =
                                  '12'
                       GROUP  BY conf_data_history.metric_value,
                                 billing_bigquery_data.labels_value,
                                 billing_bigquery_data.usage_start_time)AS test
               GROUP  BY month,
                         resource_id
               ORDER  BY resource_id) AS test
        GROUP  BY resource_id) tt
ORDER  BY resource_id

标签： sqlpostgresql

解决方案

你可以试试这个。

SELECT resource_id, 
  100 * ( (MAX(avg)-MIN(avg)) / MAX(avg) ) 
FROM SampleTable
GROUP BY resource_id

如果比较是在当前月份和上个月之间。

SELECT resource_id, 
  100 * ( MAX(CASE WHEN RN = 1 THEN avg ELSE 0 END) - MAX(CASE WHEN RN = 2 THEN avg ELSE 0 END) )
   / MAX(CASE WHEN RN = 1 THEN avg ELSE 0 END)
FROM (
  SELECT resource_id, avg, ROW_NUMBER() OVER(PARTITION BY resource_id ORDER BY month desc) RN FROM SampleTable
) AS T
group by resource_id

推荐阅读