面试问题:如何在栏目级别获得最近3个月的汇总? [英] Interview question:How to get last 3 month aggregation at column level?

问题描述

这是我在Apple现场采访中被问到的问题，这让我大吃一惊.数据是这样的:

This is the question i was being asked at Apple onsite interview and it blew my mind. Data is like this:

orderdate,unit_of_phone_sale

20190806,3000

20190704,3789

20190627,789

20190503,666

20190402,765

我必须写一个查询来获取每个月销售的结果，我们应该有最近3个月的销售值.让我在此处输入预期的输出.

I had to write a query to get the result for each month sale, we should have last 3 month sales values. Let me put the expected output here.

order_monnth,M-1_Sale, M-2_Sale, M-3_Sale

201908,3000,3789,789,666

201907,3789,789,666,765

201906,789,666,765,0

201905,666,765,0,0

201904,765,0,0

我只能按月进行销售，并通过对月份进行硬编码来使用案例陈述，这是错误的.我用力气写了这个sql，但是我做不到.

I could only got the month wise sale and and used case statement by hardcoding month which was wrong. I banged my head to write this sql, but i could not.

任何人都可以帮忙.这对我准备进行SQL采访真的很有帮助

Can anyone help on this. It will be really helpful for me to prepare for sql interviews

更新:这是我尝试过的

with abc as(
select to_char(order_date,'YYYYMM') as yearmonth,to_char(order_date,'YYYY') as year,to_char(order_date,'MM') as moth, sum(unit_of_phone_sale) as unit_sale
from t1 group by to_char(order_date,'YYYYMM'),to_char(order_date,'YYYY'),to_char(order_date,'MM'))
select yearmonth, year, case when month=01 then unit_sale else 0 end as M1_Sale,
case when month=02 then unit_sale else 0 end as M2_Sale...
case when month=12 then unit_sale else 0 end as M12_Sale
from abc

推荐答案

首先，您需要对当月的数据求和，然后使用LAG函数获取前几月的数据，如下所示:

You will first of all need to sum the month's data and then use the LAG function to get previous months' data as following:

SELECT
    ORDER_MONTH,
    LAG(UNIT_OF_PHONE_SALE, 1) OVER(
        ORDER BY
            ORDER_MONTH
    ) AS "M-1_Sale",
    LAG(UNIT_OF_PHONE_SALE, 2) OVER(
        ORDER BY
            ORDER_MONTH
    ) AS "M-2_Sale",
    LAG(UNIT_OF_PHONE_SALE, 3) OVER(
        ORDER BY
            ORDER_MONTH
    ) AS "M-3_Sale"
FROM
    (
        SELECT
            TO_CHAR(ORDERDATE, 'YYYYMM') AS ORDER_MONTH,
            SUM(UNIT_OF_PHONE_SALE) AS UNIT_OF_PHONE_SALE
        FROM
            DATAA
        GROUP BY
            TO_CHAR(ORDERDATE, 'YYYYMM')
    )
ORDER BY
    ORDER_MONTH DESC;

输出:

ORDER_   M-1_Sale   M-2_Sale   M-3_Sale
------ ---------- ---------- ----------
201908       3789        789        666
201907        789        666        765
201906        666        765
201905        765
201904

db<>小提琴演示

干杯！

-更新-

对于注释中提到的要求，以下查询将对其起作用.

For the requirement mentioned in the comments, Following query will work for it.

CTE AS (
    SELECT
        TRUNC(ORDERDATE, 'MONTH') AS ORDER_MONTH,
        SUM(UNIT_OF_PHONE_SALE) AS UNIT_OF_PHONE_SALE
    FROM
        DATAA
    GROUP BY
        TRUNC(ORDERDATE, 'MONTH')
)

SELECT
    TO_CHAR(C.ORDER_MONTH,'YYYYMM') as ORDER_MONTH,
    NVL(C1.UNIT_OF_PHONE_SALE, 0) AS "M-1_Sale",
    NVL(C2.UNIT_OF_PHONE_SALE, 0) AS "M-2_Sale",
    NVL(C3.UNIT_OF_PHONE_SALE, 0) AS "M-3_Sale"
FROM
    CTE C
    LEFT JOIN CTE C1 ON ( C1.ORDER_MONTH = ADD_MONTHS(C.ORDER_MONTH, - 1) )
    LEFT JOIN CTE C2 ON ( C2.ORDER_MONTH = ADD_MONTHS(C.ORDER_MONTH, - 2) )
    LEFT JOIN CTE C3 ON ( C3.ORDER_MONTH = ADD_MONTHS(C.ORDER_MONTH, - 3) )
ORDER BY
    C.ORDER_MONTH DESC

输出:

db .

干杯！

这篇关于面试问题:如何在栏目级别获得最近3个月的汇总?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！