sql值和对

63lcw9qa 于 2021-06-27 发布在 Hive

关注(0)|答案(2)|浏览(271)

我是sql新手，不知道怎么做。我想对类似的配对（vin，action）求一个名为“total\u spending”的coulmn的和，然后选择第一个经销商名称和参考月份年份（这样就不会产生重复项），并有一个输出，如示例：input

action  dealer_name   vin    Total_spending reference month year 
A1      D1            V1         T1             R1     M1    Y1
A2      D2            V2         T2             R1     M1    Y1
A2      D2            V2         T3             R2     M2    Y2
A3      D2            V1         T4             R1     M1    Y1
A4      D1            V2         T5             R1     M1    Y1
A2      D1            V2         T6             R1     M1    Y1
A1      D1            V1         T7             R2     M2    Y2
A4      D1            V2         T8             R2     M2    Y2
A1      D1            V1         T9             R3     M3    Y3
A3      D2            V2         T10            R1     M1    Y1
A3      D2            V1         T11            R2     M2    Y2

输出

action  dealer_name   vin    Total_spending reference month year 
A1      D1            V1         T1 + T7 + T9   R1     M1    Y1
A2      D2            V2         T2 + T3        R1     M1    Y1
A3      D2            V1         T4 + T11       R1     M1    Y1
A4      D1            V2         T5 + T8        R1     M1    Y1
A2      D1            V2         T6             R1     M1    Y1
A3      D2            V2         T10            R1     M1    Y1

    SELECT
        action,
        dealer_name,
        vin,
        SUM(total_spending) as total_spending,
        reference,
        year,
        issue_date,
        country_code
    FROM
        db_raw_irn_67634_vdt.sap_vme_pol
    GROUP BY
        action,
        dealer_name,
        vin,
        reference,
        year,
        issue_date,
        country_code

sql Hive

来源：https://stackoverflow.com/questions/58216075/sql-sum-pairs-of-values

2条答案

按热度按时间

laawzig21#

如果您的rdbms支持窗口函数，那么您可以执行一个窗口 SUM() 使用 ROW_NUMBER() 选择要显示的相关记录：

SELECT 
    action,
    dealer_name,
    vin,
    total_spending,
    reference,
    month, 
    year
FROM (
    SELECT
        action,
        dealer_name,
        vin,
        SUM(total_spending) OVER(PARTITION BY action, dealer_name, vin) total_spending,
        ROW_NUMBER() OVER(PARTITION BY action, dealer, vin ORDER BY reference, year, month) rn,
        reference,
        month, 
        year
    FROM mytable
) x
WHERE rn = 1

赞(0）回复(0）举报 2021-06-27

vuv7lop32#

SELECT action
    ,dealer_name
    ,vin
    ,SUM(total_spending)
    ,MIN(reference)
    ,MIN(month)
    ,MIN(YEAR)
FROM db_raw_irn_67634_vdt.sap_vme_pol
GROUP BY action
    ,dealer_name
    ,vin

赞(0）回复(0）举报 2021-06-27

我来回答

sql值和对

2条答案

相关问题

热门标签

最新问答