Скрипт помогающий создать PARTITION BY RANGE по полю DATE
SET LINESIZE 150
SET SERVEROUTPUT ON SIZE 1000000
WHENEVER SQLERROR CONTINUE
SPOOL C:\create_partition_date.log
declare
iter number;
l_date_start date := to_date('01-11-2011','dd-mm-yyyy');
l_date_end date := to_date('01-01-2023','dd-mm-yyyy');
l_name_part varchar2(20) := 'TEST';
l_part_field varchar2(20) := 'MY_DATE';
begin
dbms_output.put_line('PARTITION BY RANGE('||l_part_field||')');
dbms_output.put_line('(');
dbms_output.put_line(' PARTITION '||l_name_part
||'_MIN VALUES LESS THAN (to_date('''
||to_char(add_months(l_date_start,-1),'dd-mm-yyyy')
||''',''dd-mm-yyyy''))');
for j in (
with t as (
select l_date_start d1,
l_date_end d2
from dual
)
--
select decode(level,1,d1,
trunc(add_months(d1,level-1),'mm')
) as date_from
from t
connect by add_months(trunc(d1,'mm'),level-1) <= d2
)
loop
dbms_output.put_line(',PARTITION '||l_name_part||'_'
||to_char(add_months(j.date_from,-1),'mm_yyyy')
||' VALUES LESS THAN (to_date('''
||to_char(j.date_from,'dd-mm-yyyy')
||''',''dd-mm-yyyy''))');
end loop;
dbms_output.put_line(',PARTITION '||l_name_part
||'_MAX VALUES LESS THAN (MAXVALUE)');
dbms_output.put_line(')');
end;
/
spool off
PARTITION BY RANGE(MY_DATE)
(
PARTITION TEST_MIN VALUES LESS THAN (to_date('01-10-2011','dd-mm-yyyy'))
,PARTITION TEST_10_2011 VALUES LESS THAN (to_date('01-11-2011','dd-mm-yyyy'))
,PARTITION TEST_11_2011 VALUES LESS THAN (to_date('01-12-2011','dd-mm-yyyy'))
...
,PARTITION TEST_11_2022 VALUES LESS THAN (to_date('01-12-2022','dd-mm-yyyy'))
,PARTITION TEST_12_2022 VALUES LESS THAN (to_date('01-01-2023','dd-mm-yyyy'))
,PARTITION TEST_MAX VALUES LESS THAN (MAXVALUE)
)
PL/SQL procedure successfully completed
SQL> spool off
Stopped spooling to C:\create_partition_date.log
Не совсем понятно. Хотелось бы пример использования и результат.
А то мне мыслится так:
WITH ttt AS (SELECT 3 ss, TO_DATE(‘03.05.1977’, ‘dd.mm.yyyy’) dd FROM DUAL
UNION ALL
SELECT 3 ss, TO_DATE(‘21.04.1977’, ‘dd.mm.yyyy’) dd FROM DUAL
UNION ALL
SELECT 2 ss, TO_DATE(‘11.01.1976’, ‘dd.mm.yyyy’) dd FROM DUAL
UNION ALL
SELECT 1 ss, TO_DATE(‘14.11.1976’, ‘dd.mm.yyyy’) dd FROM DUAL
UNION ALL
SELECT 1 ss, TO_DATE(‘21.05.1977’, ‘dd.mm.yyyy’) dd FROM DUAL)
select ttt.*,
TRUNC(dd,’Y’) gg,
sum(ss) over (partition by TRUNC(dd,’Y’)) poGod,
TRUNC(dd,’MM’) mm,
sum(ss) over (partition by TRUNC(dd,’MM’)) poMes
from ttt