在分区内上移非空值 [英] Move Non-NULL Values Up Within Partition

查看：98
本文介绍了在分区内上移非空值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

不确定标题是否有意义，但这是我的问题以及如何到达当前位置.

Not sure if the title makes sense but here is my problem and how I got to where I am.

背景:我在一对多设置中有2个表. many表可以具有与该表关联的任何数量的记录，但是具有一个附加列，该列可以将多条记录标识为介于1到7之间的任何值.我用7个CTE来收集"每个1-7组的所有行，然后将它们连接到一个表上，使我到达示例数据的地步.

Background: I have 2 tables in a one-to-many setup. The many table can have any number of records that tie back to the one but has an additional column that can identify the many record as anything between a value of 1-7. I "gathered" all the rows for each group 1-7 with 7 CTE's and then joined them together to the one table getting me to the point of my sample data.

如果单个列中的所有行都为空，那么我想显示一个*，否则按GROUPID/GROUPNAME在每个列中显示所有不同的值.我有些骇人听闻，并以一种告诫方式大幅度削减了获取自己想要的东西的方式.如果可能的话，我想在GROUPID/GROUPNAME中分别对每一列进行排序，以使NULL不在数据中间.

If all of the rows in a single column are null then I want to display an *, otherwise display all distinct values within each column per GROUPID/GROUPNAME. I've somewhat hack and slashed my way to getting what I want with one caveat. If possible I would like to order each column individually within the GROUPID/GROUPNAME so that NULLs aren't in the middle of the data.

这是我的代码，使我陷入了困境:

Here is my code that got me to the point I am stuck at:

WITH SAMPLEDATA (CYCLEID,GROUPID,GROUPNAME,COL1,COL2,COL3,COL4,COL5,COL6,COL7)
AS (
SELECT 1,7669,'000000261','GAS',NULL,NULL,NULL,'1',NULL,'00' FROM DUAL
UNION ALL
SELECT 2,7669,'000000261','GAS',NULL,NULL,NULL,'1',NULL,'000000261' FROM DUAL
UNION ALL
SELECT 3,7669,'000000261','GAS',NULL,NULL,NULL,'Chester',NULL,'00' FROM DUAL
UNION ALL
SELECT 4,7669,'000000261','GAS',NULL,NULL,NULL,'Chester',NULL,'000000261' FROM DUAL
UNION ALL
SELECT 5,7669,'000000261','GFG',NULL,NULL,NULL,'1',NULL,'00' FROM DUAL
UNION ALL
SELECT 6,7669,'000000261','GFG',NULL,NULL,NULL,'1',NULL,'000000261' FROM DUAL
UNION ALL
SELECT 7,7669,'000000261','GFG',NULL,NULL,NULL,'Chester',NULL,'00' FROM DUAL
UNION ALL
SELECT 8,7669,'000000261','GFG',NULL,NULL,NULL,'Chester',NULL,'000000261' FROM DUAL
UNION ALL
SELECT 9,7669,'000000261','GKE',NULL,NULL,NULL,'1',NULL,'00' FROM DUAL
UNION ALL
SELECT 10,7669,'000000261','GKE',NULL,NULL,NULL,'1',NULL,'000000261' FROM DUAL
UNION ALL
SELECT 11,7669,'000000261','GKE',NULL,NULL,NULL,'Chester',NULL,'00' FROM DUAL
UNION ALL
SELECT 12,7669,'000000261','GKE',NULL,NULL,NULL,'Chester',NULL,'000000261' FROM DUAL
 ) 
 , GROUPSPROCESSED AS (
SELECT
  GROUPID,
  GROUPNAME,
  CASE
    WHEN cycleid = 1
    THEN NVL(COL1,'*')
    WHEN COL1 = FIRST_VALUE(COL1) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL1)
    THEN NULL
    WHEN COL1 = LAG(COL1) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL1)
    THEN NULL
    ELSE COL1
  END AS "COL1",
  CASE
    WHEN cycleid = 1
    THEN NVL(COL2,'*')
    WHEN COL2 = FIRST_VALUE(COL2) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL2)
    THEN NULL
    WHEN COL2 = LAG(COL2) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL2)
    THEN NULL
    ELSE COL2
  END AS "COL2",
  CASE
    WHEN cycleid = 1
    THEN NVL(COL3,'*')
    WHEN COL3 = FIRST_VALUE(COL3) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL3)
    THEN NULL
    WHEN COL3 = LAG(COL3) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL3)
    THEN NULL
    ELSE COL3
  END AS "COL3",
  CASE
    WHEN cycleid = 1
    THEN NVL(COL4,'*')
    WHEN COL4 = FIRST_VALUE(COL4) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL4)
    THEN NULL
    WHEN COL4 = LAG(COL4) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL4)
    THEN NULL
    ELSE COL4
  END AS "COL4",
  CASE
    WHEN cycleid = 1
    THEN NVL(COL5,'*')
    WHEN COL5 = FIRST_VALUE(COL5) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL5)
    THEN NULL
    WHEN COL5 = LAG(COL5) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL5)
    THEN NULL
    ELSE COL5
  END AS "COL5",
  CASE
    WHEN cycleid = 1
    THEN NVL(COL6,'*')
    WHEN COL6 = FIRST_VALUE(COL6) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL6)
    THEN NULL
    WHEN COL6 = LAG(COL6) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL6)
    THEN NULL
    ELSE COL6
  END AS "COL6",
  CASE
    WHEN cycleid = 1
    THEN NVL(COL7,'*')
    WHEN COL7 = FIRST_VALUE(COL7) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL7)
    THEN NULL
    WHEN COL7 = LAG(COL7) OVER (PARTITION BY GROUPNAME ORDER BY GROUPNAME, COL7)
    THEN NULL
    ELSE COL7
  END AS "COL7"
FROM SAMPLEDATA)
SELECT * FROM GROUPSPROCESSED
WHERE COALESCE (COL1,COL2,COL3,COL4,COL5,COL6,COL7) IS NOT NULL
ORDER BY 1,2,3,4,5,6,7,8,9

电流输出:

所需的输出:

编辑 这是代码，可以在执行任何操作之前从我的示例中导入确切的原始数据.从技术上讲，我需要将3个表捆绑在一起以破坏上面的输出.

EDIT Here is code to import the exact raw data from my example before any manipulation. It's technically 3 tables that I need to tie together to derrive the output above.

GROUPPROFILE在上面的输出中保存了字段1-2中的ID和NAME.

The GROUPPROFILE holds the ID and NAME from fields 1-2 in my output above.

COLVALUES根据COLLEVELID为1-7保留输出中其余字段的数据(NAME)

The COLVALUES holds the data (NAME) for the remainder of the fields in the output based on the COLLEVELID being 1-7

GROUPCOLMM表仅用于连接GROUPPROFILE.GROUPPROFILEID> COLVALUES.COLVALUEID

The GROUPCOLMM table is just used to connect GROUPPROFILE.GROUPPROFILEID > COLVALUES.COLVALUEID

CREATE TABLE GROUPPROFILE (GROUPPROFILEID NUMBER(12), SHORTNM NVARCHAR2(30))
REM INSERTING into GROUPPROFILE
Insert into GROUPPROFILE (GROUPPROFILEID,SHORTNM) values (7669,'000000261');

CREATE TABLE COLVALUES (COLVALUEID NUMBER(12), NAME NVARCHAR2(50), COLLEVELID NUMBER(12))
REM INSERTING into COLVALUES
Insert into COLVALUES (COLVALUEID,NAME,COLLEVELID) values (18901,'GFG',1);
Insert into COLVALUES (COLVALUEID,NAME,COLLEVELID) values (106,'GKE',1);
Insert into COLVALUES (COLVALUEID,NAME,COLLEVELID) values (1351,'GAS',1);
Insert into COLVALUES (COLVALUEID,NAME,COLLEVELID) values (423,'0',4);
Insert into COLVALUES (COLVALUEID,NAME,COLLEVELID) values (1,'1',5);
Insert into COLVALUES (COLVALUEID,NAME,COLLEVELID) values (18514,'Chester',5);

CREATE TABLE GROUPCOLMM (GROUPPROFILEID NUMBER(12), COLVALUEID NUMBER(12))
REM INSERTING into GROUPCOLMM
Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,1);
Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,3);
Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,106);
Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,1351);
Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,16503);
Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,18514);
Insert into GROUPCOLMM (GROUPPROFILEID,COLVALUEID) values (7669,18901);

推荐答案

您无法从发布的原始"数据中获取示例数据.我从子查询ORIGINALDATA中的样本数据中对原始数据进行了反向工程，然后提出了以下解决方案:

You can't get your sample data from the "raw" data you posted. I reverse engineered raw data from your sample data in the subquery ORIGINALDATA, then came up with this solution:

WITH SAMPLEDATA (CYCLEID,GROUPID,GROUPNAME,COL1,COL2,COL3,COL4,COL5,COL6,COL7)
AS (
SELECT 1,7669,'000000261','GAS',NULL,NULL,NULL,'1',NULL,'00' FROM DUAL
UNION ALL
SELECT 2,7669,'000000261','GAS',NULL,NULL,NULL,'1',NULL,'000000261' FROM DUAL
UNION ALL
SELECT 3,7669,'000000261','GAS',NULL,NULL,NULL,'Chester',NULL,'00' FROM DUAL
UNION ALL
SELECT 4,7669,'000000261','GAS',NULL,NULL,NULL,'Chester',NULL,'000000261' FROM DUAL
UNION ALL
SELECT 5,7669,'000000261','GFG',NULL,NULL,NULL,'1',NULL,'00' FROM DUAL
UNION ALL
SELECT 6,7669,'000000261','GFG',NULL,NULL,NULL,'1',NULL,'000000261' FROM DUAL
UNION ALL
SELECT 7,7669,'000000261','GFG',NULL,NULL,NULL,'Chester',NULL,'00' FROM DUAL
UNION ALL
SELECT 8,7669,'000000261','GFG',NULL,NULL,NULL,'Chester',NULL,'000000261' FROM DUAL
UNION ALL
SELECT 9,7669,'000000261','GKE',NULL,NULL,NULL,'1',NULL,'00' FROM DUAL
UNION ALL
SELECT 10,7669,'000000261','GKE',NULL,NULL,NULL,'1',NULL,'000000261' FROM DUAL
UNION ALL
SELECT 11,7669,'000000261','GKE',NULL,NULL,NULL,'Chester',NULL,'00' FROM DUAL
UNION ALL
SELECT 12,7669,'000000261','GKE',NULL,NULL,NULL,'Chester',NULL,'000000261' FROM DUAL
 ) 
, originaldata as (
  select distinct groupid, groupname, col, val from sampledata
  unpivot (val for col in (COL1 as 1,COL2 as 2,COL3 as 3,COL4 as 4,COL5 as 5,COL6 as 6,COL7 as 7))
)
select GROUPID, GROUPNAME, 
  case when rn = 1 and col1 is null then '*' else col1 end col1,
  case when rn = 1 and col2 is null then '*' else col2 end col2,
  case when rn = 1 and col3 is null then '*' else col3 end col3,
  case when rn = 1 and col4 is null then '*' else col4 end col4,
  case when rn = 1 and col5 is null then '*' else col5 end col5,
  case when rn = 1 and col6 is null then '*' else col6 end col6,
  case when rn = 1 and col7 is null then '*' else col7 end col7
from (
  select o.*,
  row_number() over(partition by groupid, groupname, col order by val) rn
  from originaldata o
)
pivot(
  max(val) 
  for col in (1 as COL1,2 as COL2,3 as COL3,4 as COL4,5 as COL5,6 as COL6,7 as COL7)
)
order by groupid, groupname, rn;

   GROUPID GROUPNAME COL1      COL2      COL3      COL4      COL5      COL6      COL7     
---------- --------- --------- --------- --------- --------- --------- --------- ---------
      7669 000000261 GAS       *         *         *         1         *         00       
      7669 000000261 GFG                                     Chester             000000261
      7669 000000261 GKE

最诚挚的问候， 炖阿什顿

Best regards, Stew Ashton

这篇关于在分区内上移非空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆