如何使用oracle sql获取多列中的第一个非零/非空值和最后一个零/非空值

This is how the data looks, here the

这就是数据的外观，这里是

"ID"    "ACTIVE"    "SERVICEID" "MONTH" "D1"    "D2"    "D3"    "D4"    "D5"    "D6"    "D7"    "D8"    "D9"    "D10"   "D11"   "D12"   "D13"   "D14"   "D15"   "D16"   "D17"   "D18"   "D19"   "D20"   "D21"   "D22"   "D23"   "D24"   "D25"   "D26"   "D27"   "D28"   "D29"   "D30"   "D31"   
352917  1   "FEUSA0001U"    199603                                              24.48   23.76   24.24   24.82           24.84   25.24   25  25  25.5            25.76   25.88   25.62   25.24   25.62               
353793  1   "FEUSA00024"    199603                                              14.92   14.77   14.8    14.78           14.57   14.75   14.75   14.75   14.75           14.75   14.75   14.56   14.56   14.69               
377994  1   "FEUSA0001X"    199603                                              59.16   58.84   59.12   59.92           59.72   60  60  61.52   61.24           61.76   61.76   62.24   62.24   62.76               
377737  1   "FEUSA00026"    199603                                                   9.89   9.9 10.01           10.01   10.12   10.12   10.25   10.25           10.25   10.25   10.25   10.25   10.25

So for the first record i need to get back First non zero / non null as D6 or 6 and for the last record non zero/ not null value should be D20 or 20.

因此，对于第一个记录，我需要返回第一个非零/非空为D6或6，而对于最后一个记录，非零/非空值应为D20或20。

Let me know if you need more information on this.

如果您需要更多相关信息，请与我们联系。

Here is the DDL for the table

这是表的DDL

CREATE TABLE "SERV" (
     "ID"           NUMBER,
     "ACTIVE"       NUMBER,
     "SERVICEID"    VARCHAR2(10 BYTE),
     "MONTH"        NUMBER(*,0),
     "D1"           NUMBER,
     "D2"           NUMBER,
     "D3"           NUMBER,
     "D4"           NUMBER,
     "D5"           NUMBER,
     "D6"           NUMBER,
     "D7"           NUMBER,
     "D8"           NUMBER,
     "D9"           NUMBER,
     "D10"          NUMBER,
     "D11"          NUMBER,
     "D12"          NUMBER,
     "D13"          NUMBER,
     "D14"          NUMBER,
     "D15"          NUMBER,
     "D16"          NUMBER,
     "D17"          NUMBER,
     "D18"          NUMBER,
     "D19"          NUMBER,
     "D20"          NUMBER,
     "D21"          NUMBER,
     "D22"          NUMBER,
     "D23"          NUMBER,
     "D24"          NUMBER,
     "D25"          NUMBER,
     "D26"          NUMBER,
     "D27"          NUMBER,
     "D28"          NUMBER,
     "D29"          NUMBER,
     "D30"          NUMBER,
     "D31"          NUMBER
)

DML sample records

DML样本记录

Insert into SERV (ID,ACTIVE,SERVICEID,MONTH,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31) values (352917,1,'FEUSA0001U',199603,null,null,null,null,null,null,null,null,null,null,null,24.48,23.76,24.24,24.82,null,null,24.84,25.24,25,25,25.5,null,null,25.76,25.88,25.62,25.24,25.62,null,null);
Insert into SERV (ID,ACTIVE,SERVICEID,MONTH,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31) values (353793,1,'FEUSA00024',199603,null,null,null,null,null,null,null,null,null,null,null,14.92,14.77,14.8,14.78,null,null,14.57,14.75,14.75,14.75,14.75,null,null,14.75,14.75,14.56,14.56,14.69,null,null);
Insert into SERV (ID,ACTIVE,SERVICEID,MONTH,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31) values (377994,1,'FEUSA0001X',199603,null,null,null,null,null,null,null,null,null,null,null,59.16,58.84,59.12,59.92,null,null,59.72,60,60,61.52,61.24,null,null,61.76,61.76,62.24,62.24,62.76,null,null);
Insert into SERV (ID,ACTIVE,SERVICEID,MONTH,D1,D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31) values (377737,1,'FEUSA00026',199603,null,null,null,null,null,null,null,null,null,null,null,9.95,9.89,9.9,10.01,null,null,10.01,10.12,10.12,10.25,10.25,null,null,10.25,10.25,10.25,10.25,10.25,null,null);

2 个解决方案

#1

You have a very poor data format. In general, having columns that are distinguished just by a number is a bad sign. The better structure is one row per value, rather than one column.

您的数据格式非常糟糕。通常，具有仅由数字区分的列是不好的标志。更好的结构是每个值一行，而不是一列。

You can do what you want using a giant case expression:

您可以使用巨型案例表达式执行您想要的操作：

select s.*,
       (case when s.d1 <> 0 then s.d1
             when s.d2 <> 0 then s.d2
             . . .
             when s.d31 <> 0 then s.d31
        end) as first_flag
from serv s;

#2

try this:

尝试这个：

SELECT ID,
       ACTIVE,
       SERVICEID,
       MONTH,
       COALESCE(D1, D2,D3,D4,D5,D6,D7,D8,D9,D10,D11,D12,D13,D14,D15,D16,D17,D18,D19,D20,D21,D22,D23,D24,D25,D26,D27,D28,D29,D30,D31) FIRST_NOTNULL,
       COALESCE(D31, D30,D29,D28,D27,D26,D25,D24,D23,D22,D21,D20,D19,D18,D17,D16,D15,D14,D13,D12,D11,D10,D9,D8,D7,D6,D5,D4,D3,D2,D1) LAST_NOTNULL
  FROM SERV

Regards!

问候！

#1