sql - 关于字符串中数字的 OracleSQL CASE 语句

Question

我有一个名为 TEST_TABLE 的表，其中有一个名为 COLUMN1 的列。该表有 2 条记录：

V.WEEKLY_2020_15
V.WEEKLY_2020_16

我正在尝试CASE statement将这些记录映射到不同的时期。例如

SELECT
CASE WHEN COLUMN1='V.WEEK_2020_ **MAXIMUM NUMBER** ' THEN 'CURRENT PERIOD' 
ELSE 'HISTORICAL PERIOD 1' END
FROM TEST_TABLE

我不确定什么是最好的方法来做到这一点。我需要从字符串末尾获取数字，然后将其与表中的其他数字进行比较。一旦它找到一个更高或更低的数字，它就可以停止搜索，因为此表中始终只有 2 个数字。

标签： sqloracle

Answer 1

您可以使用正则表达式从字符串末尾获取数字。这个获得了第三组不包含下划线的字符。

select column1, regexp_substr(column1,'[^_]+',1,3) from test_table;

或者，您可以使用 获得第二组数字regexp_substr(column1,'[0-9]+',1,2)。最好的正则表达式取决于您对可能的字符串值的了解。如果您知道数字将始终是最后 2 个字符，您可以这样做substr(column1, -2)

如果您想识别具有最高/最低/等值的行，添加应用窗口/分析函数的列是一种常见模式。

-- sample data
with test_table as (select 'V.WEEKLY_2020_15' as column1 from dual 
                    union select 'V.WEEKLY_2020_16' from dual)
-- query
SELECT column1, regexp_substr(column1,'[^_]+',1,3) as regex, max_number, 
    CASE WHEN COLUMN1=max_number THEN 'CURRENT PERIOD' 
    ELSE 'HISTORICAL PERIOD 1' END as period
FROM (select test_table.*, 
      max(column1) over (order by regexp_substr(column1,'[^_]+',1,3) desc) as max_number 
      from test_table) T;

通常数据会比您显示的更复杂 - 例如，您可能在表中为每个 primary key有 2 个句点，然后您需要对窗口函数进行分区。

-- sample data
with test_table as (select 1 as pk, 'V.WEEKLY_2020_15' as column1 from dual 
                    union select 1, 'V.WEEKLY_2020_16' from dual
                    union select 2, 'V.WEEKLY_2021_1' from dual 
                    union select 2, 'V.WEEKLY_2021_200' from dual)
-- query
SELECT pk, column1, regexp_substr(column1,'[^_]+',1,3) as regex, 
    CASE WHEN COLUMN1=max_number THEN 'CURRENT PERIOD' 
    ELSE 'HISTORICAL PERIOD 1' END as period
FROM (select test_table.*, 
      max(column1) over (partition by pk order by regexp_substr(column1,'[^_]+',1,3) desc) as max_number 
      from test_table) T;

输出：

PK	第 1 列	正则表达式	时期
1	五、WEEKLY_2020_16	16	现时阶段
1	五、WEEKLY_2020_15	15	历史时期 1
2	五、WEEKLY_2021_200	200	现时阶段
2	V.WEEKLY_2021_1	1	历史时期 1