在选择Oracle SQL的情况下计数Distinct不工作

Question

我有一个SQL问题，在这个问题中，代码不能计数不同的ID。我在下面提供了一小部分代码，并对这个问题加了粗体。

 SELECT
"RESERVATION_STAT_DAILY"."RESORT" AS "RESORT",
"RESERVATION_STAT_DAILY"."BUSINESS_DATE" AS "BUSINESS_DATE",
 to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy') AS "MONTHYEAR",
 Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "DAY",  
 Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "MONTH",
 Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE") AS "YEAR",
 "RESERVATION_STAT_DAILY"."SOURCE_CODE" AS "SOURCE_CODE",
 "RESERVATION_STAT_DAILY"."MARKET_CODE" AS "MARKET_CODE",
 "RESERVATION_STAT_DAILY"."RATE_CODE" AS "RATE_CODE",
 "RESERVATION_STAT_DAILY"."RESV_NAME_ID" AS "RESV_NAME_ID",
     (CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' 
          AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA')
          AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT')
     THEN 'GDS'
     ELSE 'Other'
END) AS "BizUnit",
COUNT(DISTINCT CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' 
          AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA')
          AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT')
     THEN "RESERVATION_STAT_DAILY"."RESV_NAME_ID" 
ELSE NULL   
END) AS "COST",

(SUM("RESERVATION_STAT_DAILY"."BUSINESS_DATE" - "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED")/(COUNT      ("RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED"))) AS "DIFF",
SUM(NVL("RESERVATION_STAT_DAILY"."NIGHTS",0)) AS "NIGHTS",
SUM(NVL("RESERVATION_STAT_DAILY"."ROOM_REVENUE",0)) AS "ROOM_REVENUE"
FROM "OPERA"."RESERVATION_STAT_DAILY" "RESERVATION_STAT_DAILY"
Where RESORT in     ('558339','558341','4856','558340','602836','HCA','HZSD', 'TAC') and
BUSINESS_DATE < SYSDATE AND EXTRACT(year FROM "RESERVATION_STAT_DAILY"."BUSINESS_DATE_CREATED") >=2016
GROUP BY
"RESERVATION_STAT_DAILY"."RESORT",
"RESERVATION_STAT_DAILY"."BUSINESS_DATE",
  to_char("RESERVATION_STAT_DAILY"."BUSINESS_DATE",'MON-yyyy'),
  Extract(day from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"),  
 Extract(month from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"),
 Extract(year from "RESERVATION_STAT_DAILY"."BUSINESS_DATE"),
  "RESERVATION_STAT_DAILY"."SOURCE_CODE",
  "RESERVATION_STAT_DAILY"."MARKET_CODE",
  "RESERVATION_STAT_DAILY"."RATE_CODE", 
  "RESERVATION_STAT_DAILY"."RESV_NAME_ID",

  (  CASE 
WHEN (("RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS') AND ("RESERVATION_STAT_DAILY"."RATE_CODE" != 'BKIT' OR "RESERVATION_STAT_DAILY"."RATE_CODE" != 'EXPEDIA'
)) THEN 'GDS'
ELSE 'Other'
END )

Answer 1

一些清理代码的一般提示，还有一个解决方案：

正如其他人所说，NOT IN条款在这里将是完美的。用它们代替那些巨大的!=比较块。您还希望COUNT和SUM函数位于CASE语句之外，如下所示。

SELECT
    ...
    CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' 
              AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA', ...)
              AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT', ...)
         THEN 'GDS'
         ELSE 'Other'
    END AS "BizUnit",
    COUNT(DISTINCT CASE WHEN "RESERVATION_STAT_DAILY"."SOURCE_CODE" = 'GDS' 
              AND "RESERVATION_STAT_DAILY"."RATE_CODE" NOT IN ('BKIT', 'EXPEDIA', ...)
              AND "RESERVATION_STAT_DAILY"."MARKET_CODE" NOT IN ('GOVG', 'ENT', ...)
         THEN "RESERVATION_STAT_DAILY"."RESV_NAME_ID"
         ELSE NULL
    END) AS "COST",
    ...
FROM
    "OPERA"."RESERVATION_STAT_DAILY" "RESERVATION_STAT_DAILY"
WHERE
    ...
GROUP BY
    ...

您的代码超过570行。有些人认为其中的1/10代码太多了。注意到我是如何剪掉那些不直接适用于你的问题的部分的？这就是如何创建一个minimal, complete, and verifiable working example.

Answer 2

只是说几句话(太长了，不能发表评论)：

1. AND ("RESERVATION_STAT_DAILY"."RATE_CODE" != 'BKIT' OR "RESERVATION_STAT_DAILY"."RATE_CODE" != 'EXPEDIA' )？想一想。什么时候不符合这个条件？薪酬代码将始终与一个或另一个值不同(通常两者都不同)，但NULL除外，其中的结果“未知”。
2. THEN COUNT(DISTINCT "RESERVATION_STAT_DAILY"."RESV_NAME_ID") ELSE COUNT(DISTINCT "RESERVATION_STAT_DAILY"."RESV_NAME_ID") END) AS "COST"。因此，在任何情况下，您都可以计算不同的RESV_NAME_ID。那为什么是CASE？
3. 当您按RESV_NAME_ID分组时，COUNT(DISTINCT "RESERVATION_STAT_DAILY"."RESV_NAME_ID")始终只能是一个组中的1。
4. sum(NVL("RESERVATION_STAT_DAILY"."NIGHTS",0))和sum(NVL("RESERVATION_STAT_DAILY"."ROOM_REVENUE",0))：SUM会忽略空值，所以您不必在添加这些零之前生成它们。和，hoever，可以是空的，所以您可能需要NVL(SUM(NIGHTS), 0)代替。
5. 至于可读性:所有大写的查询都很难阅读。要么使用小写，要么将两者混合(例如，用于SQL关键字的大写)。由于没有列包含空白或类似的内容，所以不需要引号。由于只涉及一个表，所以不需要表限定符。如果您这样做了，您应该有一个表的短别名，并使用这个而不是整个名称。您应该用缩进来格式化查询，所以我们看到了子句(FROM、GROUP BY等)。乍一看。