sql - 如何按条件除以列中的 2 个值

Question

我有一张这样的桌子

metrics                  metrics_value 

pageviews                111
bounces                  222
avgSessionsDuration      100

我需要计算新值，理想情况下不添加新列

1. avgSessionsDuration / 60 AS 分钟

2. （跳出/浏览量）*100 AS 跳出率

我想要的输出

metrics                  metrics_value 

pageviews                111
bounces                  222
avgSessionsDuration      100

bounce rate              200
minutes                  1,6

获得分钟没问题，但是使用第二个新指标我不知道该怎么做

我做了什么

#  Get minutes

SELECT metrics, metrics_value,
CASE WHEN metrics = 'avgSessionDuration' THEN metrics_value / 60
END
FROM Table

#  Get bounces and pageviews
SELECT bounces / pageviews, 
      (SELECT metrics
      WHERE metrics = 'bounces'
      ) bounces,

      (SELECT metrics
      WHERE dmetrics = 'pageviews'
      ) pageviews

FROM Table Table

感谢任何帮助

标签： sqlselectdivide

Answer 1

你可以这样做：

SELECT 
bounces.metrics / pageviews.metrics AS bounceRate
FROM  (SELECT metrics
        WHERE metrics = 'bounces'
      ) bounces,
       (SELECT metrics
         WHERE metrics = 'pageviews'
      ) pageviews