ORACLE SQL选择不删除重复项 [英] ORACLE SQL select distinct not removing duplicates

问题描述

我有下表.格式:table_name [column1，column2等.]

VENDOR_ORDERS [ORDER_ID, ORDER_CREATION_DATETIME, REGION_ID, ZIP_CODE, AMOUNT]
CALENDAR [CALENDAR_WEEK, CALENDAR_DATE]

基本上，我要实现的目标是编写一个查询，该查询将给我:

对于每个REGION_ID和DISTINCT(ZIP_CODE)

，每个CALENDAR_WEEK

COUNT(ORDER_ID)和SUM(AMOUNT)

因此结果应如下所示:

ZIP_CODE    CALENDAR_WEEK    REGION_ID    COUNT(ORDER_ID)    SUM(AMOUNT)
                            --------------------
XXXXX           01              1             50               987.45
YYYYY           01              1             25               568.32
ZZZZZ           01              1             30               555.63
MMMMM           01              1             10               099.93
XXXXX           15              1             05               999.34
YYYYY           15              1             32               339.67
ZZZZZ           15              1             21               457.23
MMMMM           15              1             88               459.99

我使用了以下代码:

SELECT
    DISTINCT(vo.ZIP_CODE)
    ,TO_CHAR(ca.CALENDAR_WEEK)
    ,TRUNC(vo.ORDER_CREATION_DATETIME) -- this column is not needed, i just added it for visualization purposes
    ,vo.REGION_ID
    ,COUNT(vo.ORDER_ID)
    ,SUM(vo.AMOUNT)
FROM
    VENDOR_ORDERS vo
    ,CALENDAR ca
WHERE   
    TRUNC(vo.ORDER_CREATION_DATETIME) = sd.CALENDAR_DATE
    AND vo.REGION_ID = 1
GROUP BY
    vo.ZIP_CODE
    ,TO_CHAR(ca.CALENDAR_WEEK)
    ,vo.ORDER_CREATION_DATETIME
    ,vc.REGION_ID;

问题是我没有获得每个CALENDAR_WEEK的DISTINCT(ZIP_CODE)，对于相同的CALENDAR_WEEK，相同的REGION_ID但不同的COUNT(ORDER_ID)和SUM(AMOUNT)，我重复了ZIP_CODE

我希望我能说清楚. 预先感谢您的帮助

解决方案

您误解了distinct是什么.它不是功能.它是select上的修饰符，它会影响所选的 all 列.因此，它的行为完全正确.

如果您想按邮政编码和周进行汇总，则这些应该是group by中唯一的两列:

SELECT vo.ZIP_CODE, TO_CHAR(ca.CALENDAR_WEEK),
       -- vo.REGION_ID
        COUNT(vo.ORDER_ID),
        SUM(vo.AMOUNT)
FROM VENDOR_ORDERS vo JOIN
     CALENDAR ca
     ON TRUNC(vo.ORDER_CREATION_DATETIME) = sd.CALENDAR_DATE
WHERE vo.REGION_ID = 1
GROUP BY vo.ZIP_CODE, TO_CHAR(ca.CALENDAR_WEEK)

假设每个邮政编码都在一个区域中，则可能还包括region_id.

I have the following tables; format: table_name[column1, column2, etc..]

VENDOR_ORDERS [ORDER_ID, ORDER_CREATION_DATETIME, REGION_ID, ZIP_CODE, AMOUNT]
CALENDAR [CALENDAR_WEEK, CALENDAR_DATE]

basically what i'm trying to achieve is writing a query that will give me:

the COUNT(ORDER_ID) and SUM(AMOUNT) per CALENDAR_WEEK for every REGION_ID and DISTINCT(ZIP_CODE)

so the results should look something like this:

ZIP_CODE    CALENDAR_WEEK    REGION_ID    COUNT(ORDER_ID)    SUM(AMOUNT)
                            --------------------
XXXXX           01              1             50               987.45
YYYYY           01              1             25               568.32
ZZZZZ           01              1             30               555.63
MMMMM           01              1             10               099.93
XXXXX           15              1             05               999.34
YYYYY           15              1             32               339.67
ZZZZZ           15              1             21               457.23
MMMMM           15              1             88               459.99

i used the following code:

SELECT
    DISTINCT(vo.ZIP_CODE)
    ,TO_CHAR(ca.CALENDAR_WEEK)
    ,TRUNC(vo.ORDER_CREATION_DATETIME) -- this column is not needed, i just added it for visualization purposes
    ,vo.REGION_ID
    ,COUNT(vo.ORDER_ID)
    ,SUM(vo.AMOUNT)
FROM
    VENDOR_ORDERS vo
    ,CALENDAR ca
WHERE   
    TRUNC(vo.ORDER_CREATION_DATETIME) = sd.CALENDAR_DATE
    AND vo.REGION_ID = 1
GROUP BY
    vo.ZIP_CODE
    ,TO_CHAR(ca.CALENDAR_WEEK)
    ,vo.ORDER_CREATION_DATETIME
    ,vc.REGION_ID;

the problem is that i'm not getting DISTINCT(ZIP_CODE) per CALENDAR_WEEK, i'm having repeated ZIP_CODE for the same CALENDAR_WEEK, same REGION_ID but different COUNT(ORDER_ID) and SUM(AMOUNT)

i hope i made myself clear. thanks in advance for the help

解决方案

You misunderstand what distinct is. It is not a function. It is a modifier on select and it affects all columns being selected. So, it is behaving exactly as it should.

If you want aggregations by zip code and week, then those are the only two columns that should be in the group by:

SELECT vo.ZIP_CODE, TO_CHAR(ca.CALENDAR_WEEK),
       -- vo.REGION_ID
        COUNT(vo.ORDER_ID),
        SUM(vo.AMOUNT)
FROM VENDOR_ORDERS vo JOIN
     CALENDAR ca
     ON TRUNC(vo.ORDER_CREATION_DATETIME) = sd.CALENDAR_DATE
WHERE vo.REGION_ID = 1
GROUP BY vo.ZIP_CODE, TO_CHAR(ca.CALENDAR_WEEK)

You could probably include region_id as well, assuming that each zip code is in one region.

这篇关于ORACLE SQL选择不删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！