在CASE语句中使用ROLLUP函数

Question

我有一个查询，需要在其中动态更改执行ROLLUP的列。

下面是示例数据：

+-----------+---------+-------+--------+-------+----------------------------+
|Location_ID|PLANT    |  ...  |COUNT_IO|TIME_IO|TIME_TARGET_OEE_100_FILTERED|
+-----------+---------+-------+--------+-------+----------------------------+
|01105123000|1        |  ...  |10      |50     |75                          |
+-----------+---------+-------+--------+-------+----------------------------+
|01105123001|1        |  ...  |13      |65     |75                          |      
+-----------+---------+-------+--------+-------+----------------------------+
|01105123002|1        |  ...  |15      |75     |65                          |
+-----------+---------+-------+--------+-------+----------------------------+
|01105123003|1        |  ...  |13      |65     |75                          |
+-----------+---------+-------+--------+-------+----------------------------+
|01101113001|2        |  ...  |40      |200    |400                         |
+-----------+---------+-------+--------+-------+----------------------------+
|01101113002|2        |  ...  |20      |100    |400                         |
+-----------+---------+-------+--------+-------+----------------------------+

期望输出(ROLLUP on LOCATION_ID)：

+-----------+---------+-------+--------+-------+
|Location_ID|PLANT    |  ...  |COUNT_IO|OEE    |
+-----------+---------+-------+--------+-------+
|01105123000|1        |  ...  |10      |66,66  |
+-----------+---------+-------+--------+-------+
|01105123001|1        |  ...  |13      |86,66  |
+-----------+---------+-------+--------+-------+
|01105123002|1        |  ...  |15      |115,38 |
+-----------+---------+-------+--------+-------+
|01105123003|1        |  ...  |13      |86,66  |
+-----------+---------+-------+--------+-------+
|NULL       |1        |  ...  |51      |87,93  |
+-----------+---------+-------+--------+-------+

期望输出(ROLLUP on PLANT)：

+-----------+---------+-------+--------+-------+
|Location_ID|PLANT    |  ...  |COUNT_IO|OEE    |
+-----------+---------+-------+--------+-------+
|01105123000|1        |  ...  |51      |87,93  |
+-----------+---------+-------+--------+-------+
|01105123001|1        |  ...  |51      |87,93  |
+-----------+---------+-------+--------+-------+
|01105123002|1        |  ...  |51      |87,93  |
+-----------+---------+-------+--------+-------+
|01105123003|1        |  ...  |51      |87,93  |
+-----------+---------+-------+--------+-------+
|01101113001|2        |  ...  |60      |37,5   |
+-----------+---------+-------+--------+-------+
|01101113002|2        |  ...  |60      |37,5   |
+-----------+---------+-------+--------+-------+
|...        |NULL     |  ...  |111     |50,92  |
+-----------+---------+-------+--------+-------+

样本代码：

SELECT
--Dimensions:
  --VALUES_FOR_TABLEAU_METADATA:
    DISTINCT LOCATION_ID,
    CONTINENT, 
    COUNTRY, 
    PLANT,
    BUSINESS_UNIT, 
    PRODUCT,
--Measures (KPI's):
  --Count:
    CASE 
        WHEN <Parameters.BU> = '%' AND <Parameters.Plant> = '%' THEN SUM(COUNT_IO) OVER (PARTITION BY BUSINESS_UNIT)
        WHEN <Parameters.BU> != '%' AND <Parameters.Plant> = '%' THEN SUM(COUNT_IO) OVER (PARTITION BY PLANT)
        ELSE SUM(COUNT_IO) OVER (PARTITION BY LOCATION_ID)
    END AS "COUNT_IO",
  --OEE:
    CASE 
        WHEN TIME_IO = 0 OR TIME_TARGET_OEE_100 = 0
        THEN 0 
        ELSE 
            CASE 
                WHEN <Parameters.BU> = '%' AND <Parameters.Plant> = '%' THEN SUM(TIME_IO) OVER (PARTITION BY BUSINESS_UNIT) *100/SUM(TIME_TARGET_OEE_100_FILTERED) OVER (PARTITION BY BUSINESS_UNIT)
                WHEN <Parameters.BU> != '%' AND <Parameters.Plant> = '%' THEN SUM(TIME_IO) OVER (PARTITION BY PLANT) *100/SUM(TIME_TARGET_OEE_100_FILTERED) OVER (PARTITION BY PLANT)
                ELSE SUM(TIME_IO) OVER (PARTITION BY LOCATION_ID) *100/SUM(TIME_TARGET_OEE_100_FILTERED) OVER (PARTITION BY LOCATION_ID)
            END
    END AS "OEE"
FROM VALUES_FOR_TABLEAU_METADATA
WHERE
  COUNT_TARGET_OEE != 0 AND PLANT LIKE <Parameters.Plant> AND BUSINESS_UNIT LIKE <Parameters.BU> AND LOCATION_ID LIKE <Parameters.LocationID> AND WORKING_DAY BETWEEN <Parameters.WorkingDay_Start> AND <Parameters.WorkingDay_End>
GROUP BY 
    CASE 
        WHEN <Parameters.BU> = '%' AND <Parameters.Plant> = '%' THEN LOCATION_ID
        WHEN <Parameters.BU> != '%' AND <Parameters.Plant> = '%' THEN LOCATION_ID
        ELSE ROLLUP(LOCATION_ID)
    END,
    CASE 
        WHEN <Parameters.BU> = '%' AND <Parameters.Plant> = '%' THEN PLANT
        WHEN <Parameters.BU> != '%' AND <Parameters.Plant> = '%' THEN ROLLUP(PLANT)
        ELSE PLANT
    END,
    CASE 
        WHEN <Parameters.BU> = '%' AND <Parameters.Plant> = '%' THEN ROLLUP(BUSINESS_UNIT)
        WHEN <Parameters.BU> != '%' AND <Parameters.Plant> = '%' THEN BUSINESS_UNIT
        ELSE BUSINESS_UNIT
    END,
    CONTINENT, COUNTRY, PRODUCT, COUNT_IO, COUNT_TARGET_OEE, TIME_IO, TIME_TARGET_OEE_100, TIME_TARGET_OEE_100_FILTERED

我猜想这个问题是由ROLLUP在CASE语句中引起的，因为到GROUP BY的代码运行良好。此外，我也不太确定我的CASE语句的用法是否正确。

这些参数看起来可能很奇怪，但它们适合在Tableau中使用，而且工作正常。

我得到的错误是：

ORA-00904：“汇总”：无效标识符

Answer 1

使用Tableau的最佳和最简单的方法是让它根据您在Tableau中表达的内容生成优化的SQL。除了在罕见的情况下，不要在Tableau中使用自定义SQL。这样你就能获得更多的灵活性和性能。

在这种情况下，我建议只从Tableau连接到VALUES_FOR_TABLEAU_METADATA。

1. 定义一个字符串值参数，允许用户为他的"rollup“(比如Dimension_For_Rollup )选择维度，其中包含两个可能的值："Location”和"Plant“。
2. 定义一个称为Selected_Dimension的计算字段，定义为if [Dimension_For_Rollup] = "Location" then [Location Id] else [Plant] end
3. 根据需要在您的viz上使用Selected_Dimension。
4. 显示Dimension_For_Rollup的参数控制

因此，用户可以根据自己的意愿在Rollup的维度之间切换，Tableau将生成优化的SQL，根据需要缓存查询。

您的Count_IO或Time_IO统计数据可以在Tableau中以不同的方式表示，可能需要Tableau的详细级别(LOD) calcs，但是--如果您不尝试预先在SQL中对所有内容进行硬编码，那么您的Tableau体验将会更好。你可以这样使用Tableau，但是你让自己的生活变得很艰难，并且放弃了大部分的好处。