sql - 在sqlite中向该表添加增长列

Question

我在sqlite3数据库中有此表Sales。

Name    Sales       Date
"Vent"  "75.998"    "2014-12-31"
"Vent"  "153.988"   "2015-12-31"
"Vent"  "180.678"   "2016-12-31"
"Vent"  "372.819"   "2017-12-31"
"DBG"   "4046.0"    "2014-12-31"
"DBG"   "4454.0"    "2015-12-31"
"DBG"   "4238.0"    "2016-12-31"
"DBG"   "4371.0"    "2017-12-31"

我想在此表中添加额外的列以显示销售增长。新表将如下所示；

Name    Sales       Date          Year1_Growth      Year2_Growth        Year3_Growth
"Vent"  "75.998"    "2014-12-31"  null              null                null 
"Vent"  "153.988"   "2015-12-31"  153.988/75.998    null                null
"Vent"  "180.678"   "2016-12-31"  180.678/153.988   180.678/75.998      null
"Vent"  "372.819"   "2017-12-31"  372.819/180.678   372.819/153.988     372.819/75.998
"DBG"   "4046.0"    "2014-12-31"  null              null                null 
"DBG"   "4454.0"    "2015-12-31"  4454/4046         null                null 
"DBG"   "4238.0"    "2016-12-31"  4238/4454         4238/4046           null 
"DBG"   "4371.0"    "2017-12-31"  4371/4238         4371/4454           4371/4046          

关于如何开始使用sql代码的任何提示？

Answer 1

我们可以通过一系列的自我联接来实现。请注意，在下面的查询中，我实际上打印出了销售数字的文本比率。这应该可以帮助您确认查询中的逻辑是正确的。如果您想要实际的数字比例，则只需使用例如s1.Sales / s2.Sales代替字符串串联。

SELECT
    s1.Name,
    s1.Sales,
    s1.Date,
    s1.Sales || '/' || s2.Sales AS first,      -- use s1.Sales / s2.Sales
    s1.Sales || '/' || s3.Sales AS second,     -- for actual ratio
    s1.Sales || '/' || s4.Sales AS third
FROM Sales s1
LEFT JOIN Sales s2
    ON s1.Name = s2.Name AND
       CAST(SUBSTR(s1.Date, 1, 4) AS int) = CAST(SUBSTR(s2.Date, 1, 4) AS int) + 1
LEFT JOIN Sales s3
    ON s1.Name = s3.Name AND
       CAST(SUBSTR(s1.Date, 1, 4) AS int) = CAST(SUBSTR(s3.Date, 1, 4) AS int) + 2
LEFT JOIN Sales s4
    ON s1.Name = s4.Name AND
       CAST(SUBSTR(s1.Date, 1, 4) AS int) = CAST(SUBSTR(s4.Date, 1, 4) AS int) + 3
ORDER BY
    s1.Name, s1.Date;

Demo