时间戳

首页 > 解决方案 > SQL - 如何将每月数据分成几周

sql - SQL - 如何将每月数据分成几周

问题描述

在我的数据库中，每年每个月都会保存特定产品的计划销售。对于其他产品，计划保存为一年中的每个星期。我必须将这两个计划统一到几周。对于基于月度的计划，我需要将其分成数周。我正在尝试使用特定月份的周数并计算每周值将每月数据分成几周，但我不知道如何获得每周表。那么使用SQL如何转换这个表

select 
   month, 
   value, 
   week_count, 
   round(value/week_count,0) weekly_value 
from 
   my_table

-=======T=======T=============T===============¬
¦ MONTH ¦ VALUE ¦  WEEK_COUNT ¦  WEEKLY_VALUE ¦
¦=======+=======+=============+===============¦
¦     1 ¦    20 ¦           4 ¦             5 ¦    
¦     2 ¦    28 ¦           4 ¦             7 ¦
¦     3 ¦    35 ¦           5 ¦             7 ¦
¦     4 ¦     8 ¦           4 ¦             2 ¦
¦     5 ¦    15 ¦           5 ¦             3 ¦
¦     6 ¦    24 ¦           4 ¦             6 ¦
¦     7 ¦    24 ¦           4 ¦             6 ¦
¦     8 ¦    20 ¦           5 ¦             4 ¦
¦     9 ¦    40 ¦           4 ¦            10 ¦
¦    10 ¦    48 ¦           4 ¦            12 ¦
¦    11 ¦    45 ¦           5 ¦             9 ¦
¦    12 ¦    36 ¦           4 ¦             9 ¦
L=======¦=======¦=============¦===============-

进入这张表：

-=======T=======¬
¦ WEEK  ¦ VALUE ¦
¦=======+=======¦
¦     1 ¦     5 ¦    
¦     2 ¦     5 ¦    
¦     3 ¦     5 ¦    
¦     4 ¦     5 ¦    
¦     5 ¦     7 ¦
¦     6 ¦     7 ¦
¦     7 ¦     7 ¦
¦     8 ¦     7 ¦
¦     9 ¦     7 ¦

...

¦    43 ¦    12 ¦
¦    44 ¦     9 ¦
¦    45 ¦     9 ¦
¦    46 ¦     9 ¦
¦    47 ¦     9 ¦
¦    48 ¦     9 ¦
¦    49 ¦     9 ¦
¦    50 ¦     9 ¦
¦    51 ¦     9 ¦
¦    52 ¦     9 ¦
L=======¦=======-

感谢您的任何建议。

标签： sqloracle

解决方案

这是一种选择；看看它是否适合您的需求。

SQL> with my_table (month, week_count, weekly_value) as
  2  (
  3   select 1,  4,  5 from dual union all
  4   select 2,  4,  7 from dual union all
  5   select 3,  5,  7 from dual union all
  6   select 4,  4,  2 from dual union all
  7   select 5,  5,  3 from dual union all
  8   select 6,  4,  6 from dual union all
  9   select 7,  4,  6 from dual union all
 10   select 8,  5,  4 from dual union all
 11   select 9,  4, 10 from dual union all
 12   select 10, 4, 12 from dual union all
 13   select 11, 5,  9 from dual union all
 14   select 12, 4,  9 from dual
 15  )
 16  select row_number() over (order by t.month) week,
 17         t.weekly_value
 18  from my_table t,
 19       table(cast(multiset(select level from dual
 20                           connect by level <= t.week_count
 21                          ) as sys.odcinumberlist ))
 22  order by 1;

      WEEK WEEKLY_VALUE
---------- ------------
         1            5
         2            5
         3            5
         4            5
         5            7
         6            7
         7            7
         8            7
         9            7
        10            7
        11            7
        12            7
        13            7
        14            2
        15            2
        16            2
        17            2
        18            3
        19            3
        20            3
        21            3
        22            3
        23            6
        24            6
        25            6
        26            6
        27            6
        28            6
        29            6
        30            6
        31            4
        32            4
        33            4
        34            4
        35            4
        36           10
        37           10
        38           10
        39           10
        40           12
        41           12
        42           12
        43           12
        44            9
        45            9
        46            9
        47            9
        48            9
        49            9
        50            9
        51            9
        52            9

52 rows selected.

SQL>

推荐阅读