sql - 如何比较两个表，以便计算两个时间段之间的增长率？

Question

我目前拥有的表如下所示：（数据来自两个不同的表，19921231、19930331）

我要创建的表如下所示（添加了第 5 列）

目标：确定每家银行的存款增长率。即比较上一季度（例如 19921231）与最近一个季度（例如 19930331）在银行的存款金额。然后以百分比的形式查看增加/减少。

这是我到目前为止写的代码：

select 
AL.repdte as `Date`, AL.cert, AL.name, AL.dep as `Deposits`
FROM usa_fdic_call_reports_1992.All_Reports_19921231_Assets_and_Liabilities as AL

UNION ALL

select 
AL.repdte as `Date`, AL.cert, AL.name, AL.dep as `Deposits`
FROM usa_fdic_call_reports_1993.All_Reports_19930331_Assets_and_Liabilities as AL

An answer to this question建议了这个代码，它可以工作但是，由于某种原因，我得到了“NULL”的输出

select al19930331.repdte as `Date`, al19930331.cert, al19930331.name,
       al19930331.dep as Deposits_1993,
       al19921231.dep as Deposits_1992,
       (al19930331.dep - al19921231.dep) / al19921231.dep as grow_rate
from usa_fdic_call_reports_1993.All_Reports_19930331_Assets_and_Liabilities as al19930331 left join
     usa_fdic_call_reports_1992.All_Reports_19921231_Assets_and_Liabilities as al19921231
     on al19930331.cert = al19921231.cert and
        al19930331.name = al19921231.name and
        al19921231.repdte = date_add(al19930331.repdte, interval 1 year);

为了隔离“NULL”问题，我将查询简化为最简单的术语，并且能够消除“NULL”问题。

现在我们有两个季度的存款列，返回似乎是正确的输出。

接下来，我从以下位置删除了最后一行代码：

select al19930331.repdte as `Date`, al19930331.cert, al19930331.name,
       al19930331.dep as Deposits_1993,
       al19921231.dep as Deposits_1992,
       (al19930331.dep - al19921231.dep) / al19921231.dep as grow_rate
from usa_fdic_call_reports_1993.All_Reports_19930331_Assets_and_Liabilities as al19930331 left join
     usa_fdic_call_reports_1992.All_Reports_19921231_Assets_and_Liabilities as al19921231
     on al19930331.cert = al19921231.cert and
        al19930331.name = al19921231.name and
        al19921231.repdte = date_add(al19930331.repdte, interval 1 year);

删除最后一行代码是可行的。运行代码会产生“除以零”错误。如何消除零误差除法？

select al19930331.repdte as `Date`, al19930331.cert, al19930331.name,
       al19930331.dep as Deposits_1993,
       al19921231.dep as Deposits_1992,
       (al19930331.dep - al19921231.dep) / al19921231.dep as grow_rate
from usa_fdic_call_reports_1993.All_Reports_19930331_Assets_and_Liabilities as al19930331 left join
     usa_fdic_call_reports_1992.All_Reports_19921231_Assets_and_Liabilities as al19921231
     on al19930331.cert = al19921231.cert and
        al19930331.name = al19921231.name

标签： sqldatetimegoogle-bigquerywindow-functions

Answer 1

您不应将此信息存储在不同的表中。这应该都在使用不同分区的同一张表中。但是由于日期嵌入在名称中间，我认为您需要使用显式join：

    select al19930331.repdte as `Date`, al19930331.cert, al19930331.name,
       al19930331.dep as Deposits_1993_0331,
       al19921231.dep as Deposits_1992_1231,
       (al19930331.dep - al19921231.dep) / al19921231.dep as grow_rate
from usa_fdic_call_reports_1993.All_Reports_19930331_Assets_and_Liabilities as al19930331 left join
     usa_fdic_call_reports_1992.All_Reports_19921231_Assets_and_Liabilities as al19921231
     on al19930331.cert = al19921231.cert and 
     al19921231.repdte = date_add(al19930331.repdte, interval 1 quarter);

使用一张表中的数据会更简单。