SQL限制数据集

Question

我有一个通过查询每天更新的数据集。该查询首先根据月末时间范围(每个月末不是规定的时间范围)和规定的时间范围(当前月末、前一个月末、当前季度末、前一个季度末等)的组合从数据库中提取11个不同的日期。

我遇到的问题是，在1/4/7/10&2/5/8/11-我们看到重复的日期流入查询。也就是说，在1月中旬，当前月末将是12月，当前季度末将是12月。或者在2月中旬，当前的“前月末”和“当前季度末”都将是12月。

这导致了Tableau下游的聚合问题。

创建的日期表如下所示：

Period    Period_Selection
12/31/18  Current Quarter End
12/31/18  Prior Month End
1/31/19   Current Month End
9/30/18   Prior Quarter End

我想要做的是编写一条case语句或where子句，将数据表限制为每个日期的单个实例。这些情况将是特定的，因此它将影响对：“当前月末”和“当前季度末”或“前一个月末”和“当前季度末”。在上面的示例中，子句将看到有两个'12/31/18‘的示例，然后删除/忽略其中一个实例，例如，删除’上一个月结束‘和保留’当前季度结束‘。

SQL示例：

SELECT PERIOD_DATE, PERIOD_SELECTION
FROM db.PERIOD_PROMPT
WHERE db_ORDER_NO IN ( '60000000', '50000000', '40000000')-- 50.. = Current Quarter end, 60.. = current month end, 40.. = prior month end

UNION ALL

SELECT ADD_MONTHS(PERIOD_DT, -3) AS PERIOD_DATE
, 'Prior Quarter End' AS PERIOD_SELECTION
FROM db.PERIOD_PROMPT 
WHERE db_ORDER_NO IN (50000000) --CURRENT QUARTER END

Answer 1

如果您希望每个PERIOD_DATE占一行，那么您可能希望使用GROUP BY PERIOD_DATE。如果您不关心选择哪个PERIOD_SELECTION，那么您可以为您选择一个聚合函数。在这里，我使用MAX来选择按字母顺序排在最后的月份(在本例中是‘PERIOD_SELECTION End’)。

-- sample data
with period_prompt as (select to_date('12/31/18', 'mm/dd/yy') as period_dt, 'Current Quarter End' as period_selection, 50000000 as db_order_no from dual
                       union all 
                       select to_date('12/31/18', 'mm/dd/yy') as period_dt, 'Prior Month End' as period_selection, 40000000 as db_order_no from dual)
select period_date, 
    max(period_selection) as period_selection
from ( -- your query
    SELECT PERIOD_DT as PERIOD_DATE, PERIOD_SELECTION
    FROM PERIOD_PROMPT
    WHERE db_ORDER_NO IN ( '60000000', '50000000', '40000000')-- 50.. = Current Quarter end, 60.. = current month end, 40.. = prior month end
    UNION ALL
    SELECT ADD_MONTHS(PERIOD_DT, -3) AS PERIOD_DATE
    , 'Prior Quarter End' AS PERIOD_SELECTION
    FROM PERIOD_PROMPT 
    WHERE db_ORDER_NO IN (50000000) --CURRENT QUARTER END
    )
group by period_date;

但是如果你对应该显示哪些特定的PERIOD_SELECTION有意见，你就需要对它们进行排序--想出一个排序算法。有时会有一个自然而然的选择--比如你可能想要DB_ORDER_NO最高的PERIOD_SELECTION。但在这种情况下，我不确定您是否有一个好的列作为排序依据，因此我们将创建一个。

在本例中，我添加了一个PRIORITY列，并对其进行了设置，使db_order_no为50000000 (当前季度末)的优先级为2，其他值为1。然后，我们调用MAX( period_selection )，但指定我们需要具有最高priority的period_selection条目。

with period_prompt as (select to_date('12/31/18', 'mm/dd/yy') as period_dt, 'Current Quarter End' as period_selection, 50000000 as db_order_no from dual
                       union all 
                       select to_date('12/31/18', 'mm/dd/yy') as period_dt, 'Prior Month End' as period_selection, 40000000 as db_order_no from dual)
select period_date, 
    max(period_selection) keep (dense_rank first order by priority desc) as period_selection
from (
    SELECT PERIOD_DT PERIOD_DATE, PERIOD_SELECTION, 
        case db_order_no
            when 50000000 then 2
            else 1
        end as priority
    FROM PERIOD_PROMPT
    WHERE db_ORDER_NO IN ( '60000000', '50000000', '40000000')-- 50.. = Current Quarter end, 60.. = current month end, 40.. = prior month end
    UNION ALL
    SELECT ADD_MONTHS(PERIOD_DT, -3) AS PERIOD_DATE
    , 'Prior Quarter End' AS PERIOD_SELECTION
    , 1 as priority
    FROM PERIOD_PROMPT 
    WHERE db_ORDER_NO IN (50000000) --CURRENT QUARTER END
    )
group by period_date;

输出：

PERIOD_DATE PERIOD_SELECTION   
----------- -------------------
30-SEP-18   Prior Quarter End  
31-DEC-18   Current Quarter End