Oracle Apex 23ai中如何为最大值柱状图设置不同颜色

我正在Oracle Apex 23ai中创建柱状图，需要为具有最大值的行对应的柱子更改颜色。我的标签字段是X_LABEL，数值字段是Y_VALUE，X_VAL用于按波兰语星期名称对柱子排序。

已实现的固定文本着色示例 #

我已经在另一图表中实现了根据X_LABEL文本值为柱子设置不同颜色，SQL代码如下：

SELECT A.*,
    case X_LABEL 
        when 'DZIEŃ WOLNY' THEN '#5F7D4F'
        WHEN 'DZIEŃ PRACUJĄCY' THEN '#CA4D3C'
    end bar_colour 
FROM (
    SELECT 'DZIEŃ PRACUJĄCY' AS X_LABEL, COUNT(WORKING_DAY) AS Y_VALUE FROM HEADACHE_RECORDS WHERE WORKING_DAY = 1
    UNION
    SELECT 'DZIEŃ WOLNY' AS X_LABEL, COUNT(WORKING_DAY) AS Y_VALUE FROM HEADACHE_RECORDS WHERE WORKING_DAY = 0
) A

当前需求：标记最大值柱子 #

现在创建的图表需要标记出所有具有最大值的柱子（可能存在多个柱子同为最大值的情况），当前使用的SQL查询如下：

SELECT
    A.*
FROM (
    SELECT 'PONIEDZIAŁEK' AS X_LABEL, 1 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 1 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'WTOREK' AS X_LABEL, 2 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 2 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'ŚRODA' AS X_LABEL, 3 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 3 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'CZWARTEK' AS X_LABEL, 4 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 4 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'PIĄTEK' AS X_LABEL, 5 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 5 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'SOBOTA' AS X_LABEL, 6 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 6 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'NIEDZIELA' AS X_LABEL, 7 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 7 AND HEADACHE_TYPE IS NOT NULL
    ORDER BY X_VAL
) A

解决方案：动态标记最大值柱子 #

通过子查询获取Y_VALUE的最大值，再用CASE语句为等于最大值的行设置特殊颜色，其余行使用默认颜色。以下提供两种实现方式：

方式1：基于原有查询修改 #

SELECT
    A.*,
    CASE WHEN A.Y_VALUE = (SELECT MAX(Y_VALUE) FROM (
        SELECT COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 1 AND HEADACHE_TYPE IS NOT NULL
        UNION
        SELECT COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 2 AND HEADACHE_TYPE IS NOT NULL
        UNION
        SELECT COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 3 AND HEADACHE_TYPE IS NOT NULL
        UNION
        SELECT COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 4 AND HEADACHE_TYPE IS NOT NULL
        UNION
        SELECT COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 5 AND HEADACHE_TYPE IS NOT NULL
        UNION
        SELECT COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 6 AND HEADACHE_TYPE IS NOT NULL
        UNION
        SELECT COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 7 AND HEADACHE_TYPE IS NOT NULL
    )) THEN '#CA4D3C' -- 最大值柱子的颜色
    ELSE '#888888' -- 默认柱子颜色
    END AS bar_colour
FROM (
    SELECT 'PONIEDZIAŁEK' AS X_LABEL, 1 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 1 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'WTOREK' AS X_LABEL, 2 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 2 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'ŚRODA' AS X_LABEL, 3 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 3 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'CZWARTEK' AS X_LABEL, 4 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 4 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'PIĄTEK' AS X_LABEL, 5 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 5 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'SOBOTA' AS X_LABEL, 6 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 6 AND HEADACHE_TYPE IS NOT NULL
    UNION
    SELECT 'NIEDZIELA' AS X_LABEL, 7 AS X_VAL, COUNT(HEADACHE_DATE) AS Y_VALUE FROM HEADACHE_RECORDS WHERE TO_CHAR(HEADACHE_DATE, 'D') = 7 AND HEADACHE_TYPE IS NOT NULL
    ORDER BY X_VAL
) A

方式2：使用CTE简化代码 #

WITH weekly_counts AS (
    SELECT 
        CASE TO_CHAR(HEADACHE_DATE, 'D')
            WHEN '1' THEN 'PONIEDZIAŁEK'
            WHEN '2' THEN 'WTOREK'
            WHEN '3' THEN 'ŚRODA'
            WHEN '4' THEN 'CZWARTEK'
            WHEN '5' THEN 'PIĄTEK'
            WHEN '6' THEN 'SOBOTA'
            WHEN '7' THEN 'NIEDZIELA'
        END AS X_LABEL,
        TO_NUMBER(TO_CHAR(HEADACHE_DATE, 'D')) AS X_VAL,
        COUNT(HEADACHE_DATE) AS Y_VALUE
    FROM HEADACHE_RECORDS
    WHERE HEADACHE_TYPE IS NOT NULL
    GROUP BY TO_CHAR(HEADACHE_DATE, 'D')
)
SELECT
    wc.*,
    CASE WHEN wc.Y_VALUE = (SELECT MAX(Y_VALUE) FROM weekly_counts) THEN '#CA4D3C' ELSE '#888888' END AS bar_colour
FROM weekly_counts wc
ORDER BY wc.X_VAL

后续Apex配置 #

修改完SQL后，在Apex图表的Series选项卡中，将bar_colour字段指定为柱子的颜色来源即可。

内容的提问来源于stack exchange，提问作者Kamil Dudar