英文:
Partition results into groups of five with dummy data?
问题
I have translated the code portion you provided into Chinese. Here it is:
SELECT * FROM (
SELECT 'top' as REC_TYPE ,* FROM TEST
UNION ALL
SELECT 'mid' as REC_TYPE ,* FROM TEST
UNION ALL
SELECT 'bot' as REC_TYPE ,* FROM TEST
) RECORD ORDER BY (ROW_NUMBER() OVER (PARTITION BY date, cd, no, REC_TYPE ORDER BY CAST(REPLACE(no, 'no',''))-1) / 5 ,date ASC ,cd ASC ,no ASC ,REC_TYPE desc ,CAST(REPLACE(no, 'no', '')) AS INT)
Please note that the code remains in English, as you requested only the translation of the code and not the content.
英文:
I have a data in a table like this:
date cd no name
------------------------------------------------------------
2023-01-01 123 100 rec1 samp name
2023-01-02 456 200 rec2 samp name1
2023-01-02 456 200 rec2 samp name2
2023-01-02 456 200 rec2 samp name3
2023-01-02 456 200 rec2 samp name4
2023-01-02 456 200 rec2 samp name5
2023-01-02 456 200 rec2 samp name6
2023-01-02 456 200 rec2 samp name7
2023-01-04 789 300 rec3 samp name
2023-01-04 789 300 rec3 samp name2
I want to come up with a SQL statement that will return three record types(top, mid, bot) for every five records. I want to group it/partition it by date, cd and no. I want every group to have five records each, so if it lacks records, I want to add dummy data.
The result I need is like this:
date cd no name REC_TYPE
----------------------------------------------------------------------------
2023-01-01 123 100 rec1 samp name top
2023-01-01 123 100 dummy top
2023-01-01 123 100 dummy top
2023-01-01 123 100 dummy top
2023-01-01 123 100 dummy top
2023-01-01 123 100 rec1 samp name mid
2023-01-01 123 100 dummy mid
2023-01-01 123 100 dummy mid
2023-01-01 123 100 dummy mid
2023-01-01 123 100 dummy mid
2023-01-01 123 100 rec1 samp name bot
2023-01-01 123 100 dummy bot
2023-01-01 123 100 dummy bot
2023-01-01 123 100 dummy bot
2023-01-01 123 100 dummy bot
2023-01-02 456 200 rec2 samp name1 top
2023-01-02 456 200 rec2 samp name2 top
2023-01-02 456 200 rec2 samp name3 top
2023-01-02 456 200 rec2 samp name4 top
2023-01-02 456 200 rec2 samp name5 top
2023-01-02 456 200 rec2 samp name1 mid
2023-01-02 456 200 rec2 samp name2 mid
2023-01-02 456 200 rec2 samp name3 mid
2023-01-02 456 200 rec2 samp name4 mid
2023-01-02 456 200 rec2 samp name5 mid
2023-01-02 456 200 rec2 samp name1 bot
2023-01-02 456 200 rec2 samp name2 bot
2023-01-02 456 200 rec2 samp name3 bot
2023-01-02 456 200 rec2 samp name4 bot
2023-01-02 456 200 rec2 samp name5 bot
2023-01-02 456 200 rec2 samp name6 top
2023-01-02 456 200 rec2 samp name7 top
2023-01-02 456 200 dummy top
2023-01-02 456 200 dummy top
2023-01-02 456 200 dummy top
2023-01-02 456 200 rec2 samp name6 mid
2023-01-02 456 200 rec2 samp name7 mid
2023-01-02 456 200 dummy mid
2023-01-02 456 200 dummy mid
2023-01-02 456 200 dummy mid
2023-01-02 456 200 rec2 samp name6 bot
2023-01-02 456 200 rec2 samp name7 bot
2023-01-02 456 200 dummy bot
2023-01-02 456 200 dummy bot
2023-01-02 456 200 dummy bot
2023-01-04 789 300 rec3 samp name top
2023-01-04 789 300 rec3 samp name 2 top
2023-01-04 789 300 dummy top
2023-01-04 789 300 dummy top
2023-01-04 789 300 dummy top
2023-01-04 789 300 rec3 samp name mid
2023-01-04 789 300 rec3 samp name 2 mid
2023-01-04 789 300 dummy mid
2023-01-04 789 300 dummy mid
2023-01-04 789 300 dummy mid
2023-01-04 789 300 rec3 samp name bot
2023-01-04 789 300 rec3 samp name 2 bot
2023-01-04 789 300 dummy bot
2023-01-04 789 300 dummy bot
2023-01-04 789 300 dummy bot
I tried using the ROW_NUMBER() and UNION and I can partition or group by 5 but I don't know how to add the lacking records or the dummy data.
SELECT * FROM ( SELECT 'top' as REC_TYPE ,* FROM TEST
UNION ALL
SELECT 'mid' as REC_TYPE ,* FROM TEST
UNION ALL
SELECT 'bot' as REC_TYPE ,* FROM TEST ) RECORD ORDER BY (ROW_NUMBER() OVER (PARTITION BY date, cd, no, REC_TYPE ORDER BY CAST(REPLACE(no, 'no','') AS INT))-1) / 5 ,date ASC ,cd ASC ,no ASC ,REC_TYPE desc ,CAST(REPLACE(no, 'no', '') AS INT)
答案1
得分: 2
以下是翻译好的部分：
使用表格中的数字（1,2,3,4,5）与记录类型（top，mid，bot）以及表格中的不同值（日期，cd，no_）进行连接。这将生成所有所需的行。现在，将您的编号数据左连接到上述行，使用coalesce()函数生成虚拟名称。
with
num(rn) as (select * from (values (1), (2), (3), (4), (5)) x(rn)),
str(type) as (select * from (values ('top'), ('mid'), ('bot')) x(type)),
dat as (select distinct date, cd, no_ from test)
select num.rn, dat.date, dat.cd, dat.no_, coalesce(t.name, 'dummy') name, str.type
from num cross join str cross join dat
left join (
select date, cd, no_, name, row_number() over (partition by cd order by name) rn
from test) t
on t.date = dat.date and t.cd = dat.cd and t.no_ = dat.no_ and t.rn = num.rn
order by date, cd, type desc, rn
英文:
Join numbers (1,2,3,4,5) with record types (top, mid, bot) and distinct (dates, cd, no_) from your table. This generates all required rows. Now left join your numbered data with above rows, use colesce() for dummy names.
with
num(rn) as (select * from (values (1), (2), (3), (4), (5)) x(rn)),
str(type) as (select * from (values ('top'), ('mid'), ('bot')) x(type)),
dat as (select distinct date, cd, no_ from test)
select num.rn, dat.date, dat.cd, dat.no_, coalesce(t.name, 'dummy') name, str.type
from num cross join str cross join dat
left join (
select date, cd, no_, name, row_number() over (partition by cd order by name) rn
from test) t
on t.date = dat.date and t.cd = dat.cd and t.no_ = dat.no_ and t.rn = num.rn
order by date, cd, type desc, rn
答案2
得分: 2
以下是翻译好的部分：
"一种解决方案是首先使用交叉应用生成虚拟行，然后使用*row_number()*进行任意编号。
可以将这些行与限制行数的表进行合并，基于现有行的计数。
最后，可以再次使用交叉应用来生成顶部/中部/底部组合，并附带一个用于排序的序列（如果需要）。
with r as (
select date, cd, no, name
from t
union all
select date, cd, no, name
from (
select date, cd, no, 'dummy' name,
Row_Number() over(partition by date, cd, no order by (select null)) rn
from t
cross apply(values (0),(0),(0),(0),(0))dupe(n)
)d
where d.rn <= 5 - (
select Count(*) from t
where d.cd = t.cd and d.no = t.no and d.date = t.date
)
)
select date, cd, no, name, rec_type
from r
cross apply(values ('top', 1), ('mid', 2), ('bot', 3))tmb(rec_type, seq)
order by date, cd, no, seq, case name when 'dummy' then 1 else 0 end;
演示 DB Fiddle"
英文:
One solution is to first generate dummy rows using a cross-apply, which are arbitrarily numbered using row_number().
This can be unioned to the table restricting the number of rows based on the count of existing rows.
Finally another cross apply can be used to generate the top/mid/bottom combinations with an accompanying sequence for ordering (if required).
with r as (
select date, cd, no, name
from t
union all
select date, cd, no, name
from (
select date, cd, no, 'dummy' name,
Row_Number() over(partition by date, cd, no order by (select null)) rn
from t
cross apply(values (0),(0),(0),(0),(0))dupe(n)
)d
where d.rn <= 5 - (
select Count(*) from t
where d.cd = t.cd and d.no = t.no and d.date = t.date
)
)
select date, cd, no, name, rec_type
from r
cross apply(values ('top', 1), ('mid', 2), ('bot', 3))tmb(rec_type, seq)
order by date, cd, no, seq, case name when 'dummy' then 1 else 0 end;
Demo DB Fiddle
答案3
得分: 1
以下是您提供的代码的翻译：
;with _list as (
select * from (
select *,count(*) OVER (PARTITION BY date_, cd, no ORDER BY CAST(REPLACE(no, 'no','')) AS INT)) as c
,(count(*) OVER (PARTITION BY date_, cd, no ORDER BY CAST(REPLACE(no, 'no','')) AS INT))/5)+1 as cc
,ROW_NUMBER() over(PARTITION BY date_, cd, no ORDER BY CAST(REPLACE(no, 'no','')) AS INT)) as rw
from Testd
)a where a.rw=1
)
, cte (date_, cd, no, name,cc, n,c) as(
SELECT date_, cd, no,'dummy' name, cc,c+1 n,c
FROM _list
where c<5 *cc
UNION ALL
SELECT date_, cd, no,'dummy' name,cc, n+1 as n,c
FROM cte
WHERE n < 5 *cc
), _listREC_TYPE as (
select 'top' as REC_TYPE union select 'mid' as REC_TYPE union select 'bot' as REC_TYPE
)
select *
from (
select date_,cd,name, no ,REC_TYPE
,(row_number() over(partition by date_, cd,no,REC_TYPE order by (
case when name='dummy' then 'z' else name end) )-1)/5 as r
from (
select *
from (
select date_,cd,name, no from cte
union all
select date_,cd,name, no from Testd
)s
)d
cross apply(select * from _listREC_TYPE)f
)f
order by date_, cd,no,r,
case when REC_TYPE='Top' then 1
when REC_TYPE='mid' then 2
when REC_TYPE='bot' then 3
else 0 end,(
case when name='dummy' then 'z' else name end)
英文:
You can make it using CTE recursive(Generate Rows) and Cross join.
First, we get the Rows that is not there
And finally we add the REC_TYPE to it
based on to my comment, a new group will be created every 5 for each group
;with _list as (
select * from (
select *,count(*) OVER (PARTITION BY date_, cd, no ORDER BY CAST(REPLACE(no, 'no','') AS INT)) as c
,(count(*) OVER (PARTITION BY date_, cd, no ORDER BY CAST(REPLACE(no, 'no','') AS INT))/5)+1 as cc
,ROW_NUMBER() over(PARTITION BY date_, cd, no ORDER BY CAST(REPLACE(no, 'no','') AS INT)) as rw
from Testd
)a where a.rw=1
)
, cte (date_, cd, no, name,cc, n,c) as(
SELECT date_, cd, no,'dummy' name, cc,c+1 n,c
FROM _list
where c<5 *cc
UNION ALL
SELECT date_, cd, no,'dummy' name,cc, n+1 as n,c
FROM cte
WHERE n < 5 *cc
), _listREC_TYPE as (
select 'top' as REC_TYPE union select 'mid' as REC_TYPE union select 'bot' as REC_TYPE
)
select *
from (
select date_,cd,name, no ,REC_TYPE
,(row_number() over(partition by date_, cd,no,REC_TYPE order by (
case when name='dummy' then 'z' else name end) )-1)/5 as r
from (
select *
from (
select date_,cd,name, no from cte
union all
select date_,cd,name, no from Testd
)s
)d
cross apply(select * from _listREC_TYPE)f
)f
order by date_, cd,no,r,
case when REC_TYPE='Top' then 1
when REC_TYPE='mid' then 2
when REC_TYPE='bot' then 3
else 0 end,(
case when name='dummy' then 'z' else name end)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论