当前位置： 文江博客话题详情

从给定模式 Oracle 构造模式

发布于 2025-01-16 11:57:59 字数 620 浏览 3 评论 0原文

我想根据存储在视图中的给定模式构建该系列。生成未来 1 年的模式。我尝试使用 row_number 和 connect by 和 Lead ，但无法构建。 模式 ID 可以是任意随机数，并且不按顺序排列。 每个循环都会跳过接下来的两个 id。

换句话说，从视图中出现的模式，我必须查看哪两个模式 id 丢失或没有日期，然后在下一次迭代中，这些模式 id 将具有日期，而接下来的两个模式将没有日期。并且很快.. 我不需要显示日期为 NULL 的日期，这也完全没问题。 我只是为了让它易于理解。

我目前使用 Oracle 12.1

输入模式

预期输出

预期输出模式

等等...

原文

I want to construct the series from a given pattern stored in a view.Generate the pattern for next 1 year. I tried to userow_number and connect by and Lead ,but was not able to construct.
The pattern id can be any random number and not in sequence.
Every cycle skip the next two ids.

In other words,from the pattern coming in the view,I have to see which two pattern id's are missing or do not have date and then in next iteration, those pattern id's will have date and the next two in sequence will not have.And so on..
I do not need to show the ones with NULL dates,that's perfectly fine too.
I just put to make it understandable.

I am currently using Oracle 12.1

Input pattern

Expected output

Expected Output Pattern

and so on...

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

东走西顾 2025-01-23 11:57:59

对于这个表（表名是“pattern”）：

PATERN_ID DATUM
--------------------
3   
4         10/11/2022
5         10/12/2022
6         10/13/2022
7         10/14/2022
10        10/15/2022
11

这个 plsql 代码：

declare 
idx number:=1;
v_min_date date;
v_end_year date;
v_date_diff number;
type t_index is table of number index by PLS_INTEGER;
type t_patern_row is table of patern%rowtype index by PLS_INTEGER;
index_null t_index;
v_patern_row t_patern_row;
num_of_repeat number;
begin
    select min(datum) into v_min_date  from patern;
    v_end_year:= trunc((v_min_date + 366),'yyyy');
    select  (to_date(v_end_year,'mm/dd/yyyy') - to_date(v_min_date,'mm/dd/yyyy')) 
    into v_date_diff from dual;
    
    select * bulk collect into v_patern_row from patern order by patern_id;
    num_of_repeat:=ceil(v_date_diff/(v_patern_row.count-2));

    for i in 1..num_of_repeat loop
        for j in v_patern_row.first..v_patern_row.last loop
            if v_patern_row(j).datum is null then
                dbms_output.put_line(v_patern_row(j).patern_id||' '||v_patern_row(j).datum);
            else 
                dbms_output.put_line(v_patern_row(j).patern_id||' '||v_min_date);
                v_min_date:=v_min_date +1;
            end if;
        end loop;
        for r in v_patern_row.first..v_patern_row.last loop
            v_patern_row(r).datum:=v_patern_row(r).datum+4;
            if v_patern_row(r).datum is null then
                index_null(idx):=r;
                idx:=idx+1;
                v_patern_row(r).datum:=to_date('11.11.1111','dd.mm.yyyy');
            end if;
        end loop;
        if index_null(1)=v_patern_row.count then
                index_null(1):=v_patern_row.first+1;
            else
                index_null(1):=index_null(1)+2;
                if index_null(1)>v_patern_row.count then
                       index_null(1):= index_null(1)-v_patern_row.count;
                end if;
        end if;
        if index_null(2)=v_patern_row.count then
                index_null(2):=v_patern_row.first+1;
            else
                index_null(2):=index_null(2)+2;
                if index_null(2)>v_patern_row.count then
                       index_null(2):= index_null(2)-v_patern_row.count;
                end if;
        end if;
        v_patern_row(index_null(1)).datum:=null;
        v_patern_row(index_null(2)).datum:=null;
    end loop;
end;

给出这个结果：

3 
4 10/11/2022
5 10/12/2022
6 10/13/2022
7 10/14/2022
10 10/15/2022
11 
3 10/16/2022
4 
5 
6 10/17/2022
7 10/18/2022
10 10/19/2022
11 10/20/2022
3 10/21/2022
4 10/22/2022
5 10/23/2022
6 
7 
10 10/24/2022
11 10/25/2022
3 10/26/2022
4 10/27/2022
5 10/28/2022
6 10/29/2022
7 10/30/2022
10 
11 
3 
4 
5 10/31/2022
6 11/01/2022
7 11/02/2022
10 11/03/2022
11 11/04/2022
3 11/05/2022
4 11/06/2022
5 
6 
7 11/07/2022
10 11/08/2022
11 11/09/2022
3 11/10/2022
4 11/11/2022
5 11/12/2022
6 11/13/2022
7 
10 
11 11/14/2022
3 
4 11/15/2022
5 11/16/2022
6 11/17/2022
7 11/18/2022
10 11/19/2022
11 
3 11/20/2022
4 
5 
6 11/21/2022
7 11/22/2022
10 11/23/2022
11 11/24/2022
3 11/25/2022
4 11/26/2022
5 11/27/2022
6 
7 
10 11/28/2022
11 11/29/2022
3 11/30/2022
4 12/01/2022
5 12/02/2022
6 12/03/2022
7 12/04/2022
10 
11 
3 
4 
5 12/05/2022
6 12/06/2022
7 12/07/2022
10 12/08/2022
11 12/09/2022
3 12/10/2022
4 12/11/2022
5 
6 
7 12/12/2022
10 12/13/2022
11 12/14/2022
3 12/15/2022
4 12/16/2022
5 12/17/2022
6 12/18/2022
7 
10 
11 12/19/2022
3 
4 12/20/2022
5 12/21/2022
6 12/22/2022
7 12/23/2022
10 12/24/2022
11 
3 12/25/2022
4 
5 
6 12/26/2022
7 12/27/2022
10 12/28/2022
11 12/29/2022
3 12/30/2022
4 12/31/2022
5 01/01/2023
6 
7 
10 01/02/2023
11 01/03/2023


PL/SQL procedure successfully completed.

它仍然适用于这种模式：

PATERN_ID DATUM
--------------------
3         10/12/2022
4   
5   
6         10/13/2022
7         10/14/2022
10        10/15/2022
11        10/16/2022

这是结果：

3 10/12/2022
4 
5 
6 10/13/2022
7 10/14/2022
10 10/15/2022
11 10/16/2022
3 10/17/2022
4 10/18/2022
5 10/19/2022
6 
7 
10 10/20/2022
11 10/21/2022
3 10/22/2022
4 10/23/2022
5 10/24/2022
6 10/25/2022
7 10/26/2022
10 
11 
3 
4 
5 10/27/2022
6 10/28/2022
7 10/29/2022
10 10/30/2022
11 10/31/2022
3 11/01/2022
4 11/02/2022
5 
6 
7 11/03/2022
10 11/04/2022
11 11/05/2022
3 11/06/2022
4 11/07/2022
5 11/08/2022
6 11/09/2022
7 
10 
11 11/10/2022
3 
4 11/11/2022
5 11/12/2022
6 11/13/2022
7 11/14/2022
10 11/15/2022
11 
3 11/16/2022
4 
5 
6 11/17/2022
7 11/18/2022
10 11/19/2022
11 11/20/2022
3 11/21/2022
4 11/22/2022
5 11/23/2022
6 
7 
10 11/24/2022
11 11/25/2022
3 11/26/2022
4 11/27/2022
5 11/28/2022
6 11/29/2022
7 11/30/2022
10 
11 
3 
4 
5 12/01/2022
6 12/02/2022
7 12/03/2022
10 12/04/2022
11 12/05/2022
3 12/06/2022
4 12/07/2022
5 
6 
7 12/08/2022
10 12/09/2022
11 12/10/2022
3 12/11/2022
4 12/12/2022
5 12/13/2022
6 12/14/2022
7 
10 
11 12/15/2022
3 
4 12/16/2022
5 12/17/2022
6 12/18/2022
7 12/19/2022
10 12/20/2022
11 
3 12/21/2022
4 
5 
6 12/22/2022
7 12/23/2022
10 12/24/2022
11 12/25/2022
3 12/26/2022
4 12/27/2022
5 12/28/2022
6 
7 
10 12/29/2022
11 12/30/2022
3 12/31/2022
4 01/01/2023
5 01/02/2023
6 01/03/2023
7 01/04/2023
10 
11

结果从表 patern 的 min(date) 到结尾那一年。

该代码仅适用于模式中的 2 个空值。

在 dbms_output.put_line() 过程所在的地方，您可以插入另一个表。

For this table (table name is "patern"):

PATERN_ID DATUM
--------------------
3   
4         10/11/2022
5         10/12/2022
6         10/13/2022
7         10/14/2022
10        10/15/2022
11

This plsql code:

declare 
idx number:=1;
v_min_date date;
v_end_year date;
v_date_diff number;
type t_index is table of number index by PLS_INTEGER;
type t_patern_row is table of patern%rowtype index by PLS_INTEGER;
index_null t_index;
v_patern_row t_patern_row;
num_of_repeat number;
begin
    select min(datum) into v_min_date  from patern;
    v_end_year:= trunc((v_min_date + 366),'yyyy');
    select  (to_date(v_end_year,'mm/dd/yyyy') - to_date(v_min_date,'mm/dd/yyyy')) 
    into v_date_diff from dual;
    
    select * bulk collect into v_patern_row from patern order by patern_id;
    num_of_repeat:=ceil(v_date_diff/(v_patern_row.count-2));

    for i in 1..num_of_repeat loop
        for j in v_patern_row.first..v_patern_row.last loop
            if v_patern_row(j).datum is null then
                dbms_output.put_line(v_patern_row(j).patern_id||' '||v_patern_row(j).datum);
            else 
                dbms_output.put_line(v_patern_row(j).patern_id||' '||v_min_date);
                v_min_date:=v_min_date +1;
            end if;
        end loop;
        for r in v_patern_row.first..v_patern_row.last loop
            v_patern_row(r).datum:=v_patern_row(r).datum+4;
            if v_patern_row(r).datum is null then
                index_null(idx):=r;
                idx:=idx+1;
                v_patern_row(r).datum:=to_date('11.11.1111','dd.mm.yyyy');
            end if;
        end loop;
        if index_null(1)=v_patern_row.count then
                index_null(1):=v_patern_row.first+1;
            else
                index_null(1):=index_null(1)+2;
                if index_null(1)>v_patern_row.count then
                       index_null(1):= index_null(1)-v_patern_row.count;
                end if;
        end if;
        if index_null(2)=v_patern_row.count then
                index_null(2):=v_patern_row.first+1;
            else
                index_null(2):=index_null(2)+2;
                if index_null(2)>v_patern_row.count then
                       index_null(2):= index_null(2)-v_patern_row.count;
                end if;
        end if;
        v_patern_row(index_null(1)).datum:=null;
        v_patern_row(index_null(2)).datum:=null;
    end loop;
end;

Gives this result:

3 
4 10/11/2022
5 10/12/2022
6 10/13/2022
7 10/14/2022
10 10/15/2022
11 
3 10/16/2022
4 
5 
6 10/17/2022
7 10/18/2022
10 10/19/2022
11 10/20/2022
3 10/21/2022
4 10/22/2022
5 10/23/2022
6 
7 
10 10/24/2022
11 10/25/2022
3 10/26/2022
4 10/27/2022
5 10/28/2022
6 10/29/2022
7 10/30/2022
10 
11 
3 
4 
5 10/31/2022
6 11/01/2022
7 11/02/2022
10 11/03/2022
11 11/04/2022
3 11/05/2022
4 11/06/2022
5 
6 
7 11/07/2022
10 11/08/2022
11 11/09/2022
3 11/10/2022
4 11/11/2022
5 11/12/2022
6 11/13/2022
7 
10 
11 11/14/2022
3 
4 11/15/2022
5 11/16/2022
6 11/17/2022
7 11/18/2022
10 11/19/2022
11 
3 11/20/2022
4 
5 
6 11/21/2022
7 11/22/2022
10 11/23/2022
11 11/24/2022
3 11/25/2022
4 11/26/2022
5 11/27/2022
6 
7 
10 11/28/2022
11 11/29/2022
3 11/30/2022
4 12/01/2022
5 12/02/2022
6 12/03/2022
7 12/04/2022
10 
11 
3 
4 
5 12/05/2022
6 12/06/2022
7 12/07/2022
10 12/08/2022
11 12/09/2022
3 12/10/2022
4 12/11/2022
5 
6 
7 12/12/2022
10 12/13/2022
11 12/14/2022
3 12/15/2022
4 12/16/2022
5 12/17/2022
6 12/18/2022
7 
10 
11 12/19/2022
3 
4 12/20/2022
5 12/21/2022
6 12/22/2022
7 12/23/2022
10 12/24/2022
11 
3 12/25/2022
4 
5 
6 12/26/2022
7 12/27/2022
10 12/28/2022
11 12/29/2022
3 12/30/2022
4 12/31/2022
5 01/01/2023
6 
7 
10 01/02/2023
11 01/03/2023


PL/SQL procedure successfully completed.

And it still works for this kind of pattern:

PATERN_ID DATUM
--------------------
3         10/12/2022
4   
5   
6         10/13/2022
7         10/14/2022
10        10/15/2022
11        10/16/2022

Here is the result:

3 10/12/2022
4 
5 
6 10/13/2022
7 10/14/2022
10 10/15/2022
11 10/16/2022
3 10/17/2022
4 10/18/2022
5 10/19/2022
6 
7 
10 10/20/2022
11 10/21/2022
3 10/22/2022
4 10/23/2022
5 10/24/2022
6 10/25/2022
7 10/26/2022
10 
11 
3 
4 
5 10/27/2022
6 10/28/2022
7 10/29/2022
10 10/30/2022
11 10/31/2022
3 11/01/2022
4 11/02/2022
5 
6 
7 11/03/2022
10 11/04/2022
11 11/05/2022
3 11/06/2022
4 11/07/2022
5 11/08/2022
6 11/09/2022
7 
10 
11 11/10/2022
3 
4 11/11/2022
5 11/12/2022
6 11/13/2022
7 11/14/2022
10 11/15/2022
11 
3 11/16/2022
4 
5 
6 11/17/2022
7 11/18/2022
10 11/19/2022
11 11/20/2022
3 11/21/2022
4 11/22/2022
5 11/23/2022
6 
7 
10 11/24/2022
11 11/25/2022
3 11/26/2022
4 11/27/2022
5 11/28/2022
6 11/29/2022
7 11/30/2022
10 
11 
3 
4 
5 12/01/2022
6 12/02/2022
7 12/03/2022
10 12/04/2022
11 12/05/2022
3 12/06/2022
4 12/07/2022
5 
6 
7 12/08/2022
10 12/09/2022
11 12/10/2022
3 12/11/2022
4 12/12/2022
5 12/13/2022
6 12/14/2022
7 
10 
11 12/15/2022
3 
4 12/16/2022
5 12/17/2022
6 12/18/2022
7 12/19/2022
10 12/20/2022
11 
3 12/21/2022
4 
5 
6 12/22/2022
7 12/23/2022
10 12/24/2022
11 12/25/2022
3 12/26/2022
4 12/27/2022
5 12/28/2022
6 
7 
10 12/29/2022
11 12/30/2022
3 12/31/2022
4 01/01/2023
5 01/02/2023
6 01/03/2023
7 01/04/2023
10 
11

The result goes from min(date) from table patern to the end of that year.

The code works only for 2 nulls in pattern.

Where the dbms_output.put_line() procedure is u can make an insert into another table.

回复 原文
你与清晨阳光 2025-01-23 11:57:59

这是我针对同一问题的sql解决方案，
这很复杂，但我不知道更好:)

with 
test as (
        select rownum abc,(minu + level - 1) date_list from 
        (select min(datum) minu from patern) min_datum
        connect by level <= 
        trunc((minu+365),'yyyy')+(((trunc((minu+365),'yyyy')-minu)/30)*9)- minu ---> Reduce this number if date goes beyond 31.12.
        ),
test2 as (
        select rownum abc,patern_id from patern
        cross join 
        (select rownum n from dual
         connect by level<=
         (select (trunc((min(datum)+365),'yyyy')- min(datum)) from patern))
        ),
test3 as (
          select ((null_row + (level*9))-10)  first_null_row, (null_row + (level*9))-9 second_null_row from (
          select max(abc) null_row from 
          (select rownum abc, datum from patern) 
          where datum is null)
          connect by level <= 
          (select (trunc((min(datum)+365),'yyyy')- min(datum)) from patern)
          ),
test4 as(
        select rownum abc, a.date_list, b.patern_id
        from test a,test2 b
        where a.abc=b.abc
        ),
test5 as(
        select 
        rownum abc,
        case 
               when a.abc in (select first_null_row from test3) or
                    a.abc in (select second_null_row from test3) then null
               else a.date_list
               end datum,
        patern_id
        from test4 a
        ),
test6 as(
        select rownum abc, patern_id from test5 where datum is not null
        )
        select b.patern_id, a.date_list
        from test4 a,test6 b
        where
        a.abc=b.abc

结果：

PATERN_ID | DATE_LIST
---------------------
3   11.10.2022
4   12.10.2022
7   13.10.2022
10  14.10.2022
11  15.10.2022
3   16.10.2022
4   17.10.2022
5   18.10.2022
6   19.10.2022
11  20.10.2022
3   21.10.2022
4   22.10.2022
5   23.10.2022
6   24.10.2022
7   25.10.2022
10  26.10.2022
4   27.10.2022
5   28.10.2022
6   29.10.2022
7   30.10.2022
10  31.10.2022
11  01.11.2022
3   02.11.2022
6   03.11.2022
7   04.11.2022
10  05.11.2022
11  06.11.2022
3   07.11.2022
4   08.11.2022
5   09.11.2022
10  10.11.2022
11  11.11.2022
3   12.11.2022
4   13.11.2022
5   14.11.2022
6   15.11.2022
7   16.11.2022
3   17.11.2022
4   18.11.2022
5   19.11.2022
6   20.11.2022
7   21.11.2022
10  22.11.2022
11  23.11.2022
5   24.11.2022
6   25.11.2022
7   26.11.2022
10  27.11.2022
11  28.11.2022
3   29.11.2022
4   30.11.2022
7   01.12.2022
10  02.12.2022
11  03.12.2022
3   04.12.2022
4   05.12.2022
5   06.12.2022
6   07.12.2022
11  08.12.2022
3   09.12.2022
4   10.12.2022
5   11.12.2022
6   12.12.2022
7   13.12.2022
10  14.12.2022
4   15.12.2022
5   16.12.2022
6   17.12.2022
7   18.12.2022
10  19.12.2022
11  20.12.2022
3   21.12.2022
6   22.12.2022
7   23.12.2022
10  24.12.2022
11  25.12.2022
3   26.12.2022
4   27.12.2022
5   28.12.2022
10  29.12.2022
11  30.12.2022
3   31.12.2022

PS。它不像我发布的 plsql 代码那样灵活，对于每个 patern_id 超过 2 个空值，它不起作用，有时它会稍微超出 31.12。模式年份（如果超过 31.12，我标记了调节日期的代码行。只需减少一点数字）

Here is my sql solution for the same problem,
it is very complicated but i don t know better :)

with 
test as (
        select rownum abc,(minu + level - 1) date_list from 
        (select min(datum) minu from patern) min_datum
        connect by level <= 
        trunc((minu+365),'yyyy')+(((trunc((minu+365),'yyyy')-minu)/30)*9)- minu ---> Reduce this number if date goes beyond 31.12.
        ),
test2 as (
        select rownum abc,patern_id from patern
        cross join 
        (select rownum n from dual
         connect by level<=
         (select (trunc((min(datum)+365),'yyyy')- min(datum)) from patern))
        ),
test3 as (
          select ((null_row + (level*9))-10)  first_null_row, (null_row + (level*9))-9 second_null_row from (
          select max(abc) null_row from 
          (select rownum abc, datum from patern) 
          where datum is null)
          connect by level <= 
          (select (trunc((min(datum)+365),'yyyy')- min(datum)) from patern)
          ),
test4 as(
        select rownum abc, a.date_list, b.patern_id
        from test a,test2 b
        where a.abc=b.abc
        ),
test5 as(
        select 
        rownum abc,
        case 
               when a.abc in (select first_null_row from test3) or
                    a.abc in (select second_null_row from test3) then null
               else a.date_list
               end datum,
        patern_id
        from test4 a
        ),
test6 as(
        select rownum abc, patern_id from test5 where datum is not null
        )
        select b.patern_id, a.date_list
        from test4 a,test6 b
        where
        a.abc=b.abc

Result:

PATERN_ID | DATE_LIST
---------------------
3   11.10.2022
4   12.10.2022
7   13.10.2022
10  14.10.2022
11  15.10.2022
3   16.10.2022
4   17.10.2022
5   18.10.2022
6   19.10.2022
11  20.10.2022
3   21.10.2022
4   22.10.2022
5   23.10.2022
6   24.10.2022
7   25.10.2022
10  26.10.2022
4   27.10.2022
5   28.10.2022
6   29.10.2022
7   30.10.2022
10  31.10.2022
11  01.11.2022
3   02.11.2022
6   03.11.2022
7   04.11.2022
10  05.11.2022
11  06.11.2022
3   07.11.2022
4   08.11.2022
5   09.11.2022
10  10.11.2022
11  11.11.2022
3   12.11.2022
4   13.11.2022
5   14.11.2022
6   15.11.2022
7   16.11.2022
3   17.11.2022
4   18.11.2022
5   19.11.2022
6   20.11.2022
7   21.11.2022
10  22.11.2022
11  23.11.2022
5   24.11.2022
6   25.11.2022
7   26.11.2022
10  27.11.2022
11  28.11.2022
3   29.11.2022
4   30.11.2022
7   01.12.2022
10  02.12.2022
11  03.12.2022
3   04.12.2022
4   05.12.2022
5   06.12.2022
6   07.12.2022
11  08.12.2022
3   09.12.2022
4   10.12.2022
5   11.12.2022
6   12.12.2022
7   13.12.2022
10  14.12.2022
4   15.12.2022
5   16.12.2022
6   17.12.2022
7   18.12.2022
10  19.12.2022
11  20.12.2022
3   21.12.2022
6   22.12.2022
7   23.12.2022
10  24.12.2022
11  25.12.2022
3   26.12.2022
4   27.12.2022
5   28.12.2022
10  29.12.2022
11  30.12.2022
3   31.12.2022

PS. it is not flexible like plsql code I posted, it won t work for more then 2 nulls per patern_id and sometimes it will go little bit beyond 31.12. of the pattern year (I marked the line of code that regulates date if it goes beyond 31.12. just reduce the number little bit)

回复 原文
~没有更多了~

关于作者

︶￣淡然

暂无简介

文章
评论
26 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

十二

文章 0 评论 0

飞烟轻若梦

文章 0 评论 0

OPleyuhuo

文章 0 评论 0

wxb0109

文章 0 评论 0

旧城空念

文章 0 评论 0

-小熊_

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文