时间戳

首页 > 解决方案 > SQL 查询没有返回正确的结果 - SQLite

sql - SQL 查询没有返回正确的结果 - SQLite

问题描述

我有三张桌子：

充电量：

cust_sign_month      month      months_since_cust      country      Charge_volume
----------------------------------------------------------------------------------------
2018-01              2018-01        1                      DE           100
2018-01              2018-02        1                      DE           100
2018-01              2018-03        1                      DE           100
2019-01              2019-01        1                      IN           100
2019-02              2019-02        1                      IN           1,000
2019-03              2019-03        1                      IN           1,000
2019-04              2019-04        1                      IN           1,000

Active_Customers：

    cust_sign_month      month      months_since_cust      country      perc_active_cust
    2018-01              2018-01        1                      DE           72%
    2018-01              2018-02        1                      DE           71%
    2018-01              2018-03        1                      DE           69%
    2019-01              2019-01        1                      IN           72%
    2019-02              2019-02        1                      IN           71%
    2019-03              2019-03        1                      IN           80%
    2019-04              2019-04        1                      IN           90%

New_customers：

     month          country      new_cust_onboarded
     -----------------------------------------------
     2018-01             DE           40,000
     2018-02             DE           41,000
     2018-03             DE           42,000
     2019-01             IN           43,000
     2019-02             IN           30,000
     2019-03             IN           21,000
     2019-04             IN           12,000

JOIN 条件：将分别在 onActive_customers和month 加入。并且将加入两个表中的所有公共键New_Customerscust_sign_monthCharge_volumeActive_customers

我的问题是，我需要按从 2019-01 到 2019-04 的月份来获取 2019 年的总收费量。

输出应如下所示：

Activity_Month        Total_transactions_2019
---------------------------------------------
2019-01                 309600
2019-02               23430000
2019-03               18480000
2019-04               11880000

我已经编写了以下查询，但它没有返回任何结果，并且出现错误

SQL 错误 [1]: [SQLITE_ERROR] SQL 错误或缺少数据库（不明确的列名：月份）

我的查询：

SELECT 
    a.month AS Activity_Month,
    SUM(REPLACE(a.Charge_volume, ',', '') * (REPLACE(c.new_cust_onboarded , ',', '') * b.perc_active_cust) AS Total_transactions_2019
FROM 
    Charge_volume a 
JOIN 
    Active_Customers b 
JOIN 
    New_customers c ON a.cust_sign_month = b.cust_sign_month
                    AND a.country = b.country
                    AND a.month = b.month
                    AND a.months_since_cust = b.months_since_cust
                    AND b.cust_sign_month = c.month    
                    AND b.country = c.country 
                    AND substring(a.month, 1, 4) = '2019'
GROUP BY 
    month
ORDER BY 
    month

标签： sqlsqlite

解决方案

这是您正确且有效的 SQL 查询。但是SUM函数中的表达式并没有给出预期的结果。也许您需要将perc_active_cust列的值除以 100。

SELECT 
    a.month AS Activity_Month,
    SUM(
      REPLACE(a.Charge_volume, ',', '') * 
      REPLACE(c.new_cust_onboarded , ',', '') * (b.perc_active_cust / 100)
    ) AS Total_transactions_2019
FROM Charge_volume a 
JOIN Active_Customers b 
     ON a.cust_sign_month = b.cust_sign_month 
    AND a.country = b.country 
    AND a.month = b.month 
    AND a.months_since_cust = b.months_since_cust
JOIN New_customers c 
     ON b.cust_sign_month = c.month    
    AND b.country = c.country 
WHERE a.month BETWEEN '2019-01' AND '2019-04'
GROUP BY a.month
ORDER BY a.month

请注意，该substring函数在 SQLite 中不存在。

小提琴

推荐阅读