获取oracle中具有特定值的列的范围

eblbsuwk 于 2021-07-27 发布在 Java

关注(0)|答案(3)|浏览(291)

我不知道如何命名这个问题，但我有一个oracle表，它有一周中每一天的列。这代表了一家公司的营业日。这些列中的值是“y”或“n”。这是几年前table的设置方式，我无法更改它。

ID | MONDAY | TUESDAY | WEDNESDAY | THURSDAY | FRIDAY | SATURDAY | SUNDAY
1      Y         Y          Y          Y          Y        N          N
2      N         Y          Y          Y          Y        Y          N

我需要能够查询这个表并得到如下结果，但不知道从哪里开始。

ID  |     DAYS_OPEN
1      MONDAY - FRIDAY
2      TUESDAY - SATURDAY

这在查询中是可能的还是我需要使用函数？如果一个函数能使它更容易，我也会很好的解决方案。

sql oracle plsql

来源：https://stackoverflow.com/questions/63193357/get-range-of-columns-with-specific-value-in-oracle

3条答案

按热度按时间

13z8s7eq1#

找到了更简单的解决方案：

replace(
      trim( 
        '.' from
        replace(replace(replace(replace(replace(replace(replace(
            regexp_replace(
                   decode(MONDAY    ,'Y','1','.') ||
                   decode(TUESDAY   ,'Y','2','.') || 
                   decode(WEDNESDAY ,'Y','3','.') || 
                   decode(THURSDAY  ,'Y','4','.') || 
                   decode(FRIDAY    ,'Y','5','.') || 
                   decode(SATURDAY  ,'Y','6','.') || 
                   decode(SUNDAY    ,'Y','7','.')                   
                  ,'(\d)\d*(\d)','\1-\2')
           ,'1','MONDAY')
           ,'2','TUESDAY')
           ,'3','WEDNESDAY')
           ,'4','THURSDAY')
           ,'5','FRIDAY')
           ,'6','SATURDAY')
           ,'7','SUNDAY')
        ),'.',',')

完整示例：

with t(ID, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY) as (
   select 1,'Y', 'Y', 'Y', 'Y', 'Y' ,'N', 'N' from dual union all
   select 2,'N', 'Y', 'Y', 'Y', 'Y' ,'Y', 'N' from dual union all
   select 3,'Y', 'N', 'Y', 'Y', 'N' ,'Y', 'Y' from dual
)
select
   id
  ,replace(
      trim( 
        '.' from
        replace(replace(replace(replace(replace(replace(replace(
            regexp_replace(
                   decode(MONDAY    ,'Y','1','.') ||
                   decode(TUESDAY   ,'Y','2','.') || 
                   decode(WEDNESDAY ,'Y','3','.') || 
                   decode(THURSDAY  ,'Y','4','.') || 
                   decode(FRIDAY    ,'Y','5','.') || 
                   decode(SATURDAY  ,'Y','6','.') || 
                   decode(SUNDAY    ,'Y','7','.')                   
                  ,'(\d)\d*(\d)','\1-\2')
           ,'1','MONDAY')
           ,'2','TUESDAY')
           ,'3','WEDNESDAY')
           ,'4','THURSDAY')
           ,'5','FRIDAY')
           ,'6','SATURDAY')
           ,'7','SUNDAY')
        ),'.',',')
     as sch
from t;

结果：

ID SCH
---------- ------------------------------------------------------------
         1 MONDAY-FRIDAY
         2 TUESDAY-SATURDAY
         3 MONDAY,WEDNESDAY-THURSDAY,SATURDAY-SUNDAY

编辑：在原始答案的基础上扩展，将相同的过程应用于一年中的几个月。我不得不在解码中把月数用方括号括起来。然后在regexp\u replace中，我指定了一个不匹配的列表（句点和空格）。
下面是完整的示例：

with t(ID, JANUARY, FEBRUARY, MARCH, APRIL, MAY, JUNE, JULY, AUGUST, SEPTEMBER, OCTOBER, NOVEMBER, DECEMBER ) as (
   select 1,'Y', 'Y', 'Y', 'Y', 'Y' ,'N', 'N', 'Y','Y','Y','Y','Y' from dual union all
   select 2,'Y', 'Y', 'Y', 'Y', 'N' ,'N', 'N', 'N','Y','Y','Y','Y' from dual union all
   select 3,'N', 'N', 'Y', 'N', 'N' ,'Y', 'Y', 'N','N','N','Y','N' from dual union all
   select 4,'Y', 'N', 'Y', 'N', 'Y' ,'N', 'Y', 'N','Y','N','Y','N' from dual
)
select
   id,
regexp_replace(
          trim( '.' from
            replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
            regexp_replace(
                       decode(JANUARY    ,'Y','01','.') ||
                       decode(FEBRUARY   ,'Y','02','.') || 
                       decode(MARCH      ,'Y','03','.') || 
                       decode(APRIL      ,'Y','04','.') || 
                       decode(MAY        ,'Y','05','.') || 
                       decode(JUNE       ,'Y','06','.') || 
                       decode(JULY       ,'Y','07','.') || 
                       decode(AUGUST     ,'Y','08','.')|| 
                       decode(SEPTEMBER  ,'Y','09','.')|| 
                       decode(OCTOBER    ,'Y','10','.')|| 
                       decode(NOVEMBER   ,'Y','11','.')|| 
                       decode(DECEMBER   ,'Y','12','.')                  
                      ,'(\d\d)\d*(\d\d)','\1-\2')
               ,'01','Jan')
               ,'02','Feb')
               ,'03','Mar')
               ,'04','Apr')
               ,'05','May')
               ,'06','Jun')
               ,'07','Jul')
               ,'08','Aug')
               ,'09','Sep')
               ,'10','Oct')
               ,'11','Nov')
               ,'12','Dec')
              )
            ,'\.+',', ') MNTHS
            from t;

结果是：

ID  MNTHS
1   Jan-May, Aug-Dec 
2   Jan-Apr, Sep-Dec 
3   Mar, Jun-Jul, Nov
4   Jan, Mar, May, Jul, Sep, Nov

赞(0）回复(0）举报 2021-07-27

uelo1irk2#

嗯，你的数据格式很糟糕。你可以用一个巨人 case 表达式：

(case MONDAY || TUESDAY || WEDNESDAY || THURSDAY || FRIDAY || SATURDAY || SUNDAY
    when 'YYYYYNN' then 'MONDAY - FRIDAY'
    when 'NYYYYYN' then 'TUESDAY - SATURDAY'
    else 'Something else!'
 end)

只有2^7=128个可能的组合，所以这在一个巨人身上是可能的 case . 您还可以将这些值放在一个引用表中并连接到该表。

赞(0）回复(0）举报 2021-07-27

kwvwclae3#

首先，您需要使用pivot转换数据：

with t(ID, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY) as (
   select 1,'Y', 'Y', 'Y', 'Y', 'Y' ,'N', 'N' from dual union all
   select 2,'N', 'Y', 'Y', 'Y', 'Y' ,'Y', 'N' from dual
)
,tpivot as (
  select 
    id, 
    weekday, 
    decode(weekday,'MONDAY',1, 'TUESDAY', 2, 'WEDNESDAY', 3, 'THURSDAY', 4, 'FRIDAY', 5, 'SATURDAY', 6, 'SUNDAY',7) weekday_n,
    val
  from t
  unpivot
    (
    val for weekday in (MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY)
    ) v
)
select * from tpivot;

结果：

ID WEEKDAY    WEEKDAY_N V
---------- --------- ---------- -
         1 MONDAY             1 Y
         1 TUESDAY            2 Y
         1 WEDNESDAY          3 Y
         1 THURSDAY           4 Y
         1 FRIDAY             5 Y
         1 SATURDAY           6 N
         1 SUNDAY             7 N
         2 MONDAY             1 N
         2 TUESDAY            2 Y
         2 WEDNESDAY          3 Y
         2 THURSDAY           4 Y
         2 FRIDAY             5 Y
         2 SATURDAY           6 Y
         2 SUNDAY             7 N

然后，您可以筛选val列中有“y”的天数并将其聚合：

with t(ID, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY) as (
   select 1,'Y', 'Y', 'Y', 'Y', 'Y' ,'N', 'N' from dual union all
   select 2,'N', 'Y', 'Y', 'Y', 'Y' ,'Y', 'N' from dual union all
   select 3,'Y', 'N', 'Y', 'Y', 'N' ,'Y', 'Y' from dual
)
,tpivot as (
  select 
    id, 
    weekday, 
    decode(weekday,'MONDAY',1, 'TUESDAY', 2, 'WEDNESDAY', 3, 'THURSDAY', 4, 'FRIDAY', 5, 'SATURDAY', 6, 'SUNDAY',7) weekday_n,
    val
  from t
  unpivot
    (
    val for weekday in (MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY)
    ) v
)
, filtered as (
   select 
      id,
      weekday,
      weekday_n,
      weekday_n - row_number()over(partition by id order by weekday_n) rn,
      val
   from tpivot
   where val='Y'
)
, agg as (
   select
      id, 
      min(weekday)keep(dense_rank first order by weekday_n) d_first,
      min(weekday)keep(dense_rank last order by weekday_n) d_last,
      count(*) cnt
   from filtered
   group by id,rn
)
select * from agg;

结果：

ID D_FIRST   D_LAST           CNT
---------- --------- --------- ----------
         1 MONDAY    FRIDAY             5
         2 TUESDAY   SATURDAY           5
         3 MONDAY    MONDAY             1
         3 WEDNESDAY THURSDAY           2
         3 SATURDAY  SUNDAY             2

最后一步是按id聚合所有值：

with t(ID, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY) as (
   select 1,'Y', 'Y', 'Y', 'Y', 'Y' ,'N', 'N' from dual union all
   select 2,'N', 'Y', 'Y', 'Y', 'Y' ,'Y', 'N' from dual union all
   select 3,'Y', 'N', 'Y', 'Y', 'N' ,'Y', 'Y' from dual
)
,tpivot as (
  select 
    id, 
    weekday, 
    decode(weekday,'MONDAY',1, 'TUESDAY', 2, 'WEDNESDAY', 3, 'THURSDAY', 4, 'FRIDAY', 5, 'SATURDAY', 6, 'SUNDAY',7) weekday_n,
    val
  from t
  unpivot
    (
    val for weekday in (MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY)
    ) v
)
, filtered as (
   select 
      id,
      weekday,
      weekday_n,
      weekday_n - row_number()over(partition by id order by weekday_n) rn,
      val
   from tpivot
   where val='Y'
)
, agg as (
   select
      id, 
      min(weekday_n)keep(dense_rank first order by weekday_n) d_first_n,
      min(weekday)keep(dense_rank first order by weekday_n) d_first,
      min(weekday)keep(dense_rank last order by weekday_n) d_last,
      count(*) cnt
   from filtered
   group by id,rn
)
select 
  id,
  listagg(
    case when cnt=1 then d_first
         else d_first||'-'||d_last
    end,
    ','
  ) within group(order by d_first_n) sched
from agg
group by id;

结果：

ID SCHED
---------- ------------------------------------------------------------
         1 MONDAY-FRIDAY
         2 TUESDAY-SATURDAY
         3 MONDAY,WEDNESDAY-THURSDAY,SATURDAY-SUNDAY

赞(0）回复(0）举报 2021-07-27

我来回答

获取oracle中具有特定值的列的范围

3条答案

相关问题

热门标签

最新问答