查询-计算具有相同列的两个DB表之间的差异

Question

如何使用查询计算具有相同列的两个DB表之间的差异？它们都有"app"、"installs"one_answers"usage"列。提前谢谢。

我的不工作查询

WITH apps AS (
SELECT app, installs_curr, usage_curr FROM (
SELECT app, installs AS installs_curr, usage AS usage_curr
FROM e5a.apps_2020_07
)
SELECT app, installs_prev, usage_prev FROM (
SELECT app, installs AS installs_prev, usage AS usage_prev
FROM e5a.apps_2020_06
)
)
SELECT app, installs_curr – installs_prev AS installs_delta, usage_curr – usage_prev AS usage_delta FROM apps
ORDER BY usage_delta DESC

**TABLE e5a.apps_2020_07**
app   installs usage 
snap  20       10
**TABLE e5a.apps_2020_06**
app   installs usage 
snap  12       8

**Result through calculation**
app   installs usage 
snap  8       2

Answer 1

这取决于您希望如何处理一个表中的值，而不是另一个表。一个好的起点是join:

SELECT a7.app,
(a7.installs - a6.installs) AS installs_diff,
(a7.usage - a6.usage) AS usage_diff
FROM e5a.apps_2020_07 a7 JOIN
e5a.apps_2020_08 a6
ON a7.apps = a6.apps;

这只包括两个表中的应用程序。

您应该修复您的数据模型，以便只有一个以月份为列的表。

Answer 2

假设App是主键，那么您可以简单地减去：

SELECT a.app, a.installs - b.installs installs_curr, a.usage - b.usage  AS usage_curr
FROM e5a.apps_2020_07 a inner join e5a.apps_2020_06 b on (a.app = b.app)