ORACLE 根据时间段取的所有 月---周 分组统计 月份周和自然周、月

获取日期列表:

SELECT TO_CHAR(TO_DATE('2014-10-01', 'yyyy-MM-dd') + ROWNUM - 1, 'yyyyMMdd') as daylist  
  FROM DUAL  
CONNECT BY ROWNUM <=  
           trunc(to_date('2015-06-01', 'yyyy-MM-dd') -  
                 to_date('2014-10-01', 'yyyy-MM-dd')) + 1  

获取月份列表:

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2014-10', 'yyyy-MM'), ROWNUM - 1),  
               'yyyyMM') as monthlist  
  FROM DUAL  
CONNECT BY ROWNUM <=  
           months_between(to_date('2015-06', 'yyyy-MM'),  
                          to_date('2014-10', 'yyyy-MM')) + 1  

获取周列表:   获取自然周    以周一开始  - 周日结束

SELECT trunc(to_DATE('2018-09-02', 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'iw') AS mon,
       trunc(to_DATE('2018-09-02', 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'iw') + 6 AS sun
  FROM DUAL
CONNECT BY ROWNUM <=
           (trunc(to_DATE('2018-10-31', 'YYYY-MM-DD'), 'iw') + 6 -
           trunc(to_DATE('2018-09-02', 'YYYY-MM-DD'), 'iw')) / 7 + 1

获取年份列表:

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2014-10', 'yyyy-MM'), (ROWNUM - 1) * 12),  
               'yyyy') as yearlist  
  FROM DUAL  
CONNECT BY ROWNUM <=  
           months_between(to_date('2015-06', 'yyyy-MM'),  
                          to_date('2014-10', 'yyyy-MM')) / 12 + 1  

 

分组统计,按照天、月份周和自然周、月、季度和年

https://blog.csdn.net/JavaAlpha/article/details/52277270


-- 按自然周的日期统计 
select to_char(next_day(t.CREATED+15/24 - 7,2),'YYYY-MM-DD') AS 周,sum(1) as 数量
from TB_EXT_TRADE t
WHERE
	t.TID LIKE 'SC%' OR t.TID LIKE 'WSC%'
group by to_char(next_day(t.CREATED+15/24 - 7,2),'YYYY-MM-DD')
ORDER BY 周;

分组统计周  自然周  select to_char(next_day(to_DATE('2018-09-02', 'YYYY-MM-DD') + 15 / 24 - 7, 2),'YYYY-MM-DD') from dual;   和  select trunc(to_DATE('2018-09-02', 'YYYY-MM-DD'),'iw') from dual;  取到的值 是一样的  获取该日期所在自然周的周一            自然周  以周一开始  周 日 结束

 

当前日期为当年第几周,几天,几月,几季度;ww,iw,w区别

http://blog.csdn.net/liuao107329/article/details/53516269 

要按、天和年对给定的时间段进行分组,可以使用Java中的日期时间库来处理。以下是一个示例代码,展示了如何按照不同的时间单位进行分组: ```java import java.time.LocalDate; import java.time.LocalDateTime; import java.time.YearMonth; import java.time.temporal.ChronoUnit; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class GroupByTimeExample { public static void main(String[] args) { // 假设有一个起始日期和一个结束日期 LocalDate startDate = LocalDate.of(2022, 1, 1); LocalDate endDate = LocalDate.of(2022, 12, 31); // 创建四个Map来存储按不同时间单位分组的结果 Map<YearMonth, List<LocalDate>> groupedByMonth = new HashMap<>(); Map<LocalDate, List<LocalDate>> groupedByDay = new HashMap<>(); Map<Integer, List<LocalDate>> groupedByWeek = new HashMap<>(); Map<Integer, List<LocalDate>> groupedByYear = new HashMap<>(); // 遍历时间段内的每一天,将每个日期按照不同的时间单位添加到对应的组中 LocalDate currentDate = startDate; while (!currentDate.isAfter(endDate)) { YearMonth yearMonth = YearMonth.from(currentDate); if (!groupedByMonth.containsKey(yearMonth)) { groupedByMonth.put(yearMonth, new ArrayList<>()); } groupedByMonth.get(yearMonth).add(currentDate); if (!groupedByDay.containsKey(currentDate)) { groupedByDay.put(currentDate, new ArrayList<>()); } groupedByDay.get(currentDate).add(currentDate); int week = currentDate.get(ChronoUnit.WEEK_OF_YEAR); if (!groupedByWeek.containsKey(week)) { groupedByWeek.put(week, new ArrayList<>()); } groupedByWeek.get(week).add(currentDate); int year = currentDate.getYear(); if (!groupedByYear.containsKey(year)) { groupedByYear.put(year, new ArrayList<>()); } groupedByYear.get(year).add(currentDate); currentDate = currentDate.plusDays(1); // 增加一天 } // 打印按不同时间单位分组的结果 System.out.println("Grouped by Month:"); for (Map.Entry<YearMonth, List<LocalDate>> entry : groupedByMonth.entrySet()) { System.out.println(entry.getKey() + ": " + entry.getValue()); } System.out.println("\nGrouped by Day:"); for (Map.Entry<LocalDate, List<LocalDate>> entry : groupedByDay.entrySet()) { System.out.println(entry.getKey() + ": " + entry.getValue()); } System.out.println("\nGrouped by Week:"); for (Map.Entry<Integer, List<LocalDate>> entry : groupedByWeek.entrySet()) { System.out.println("Week " + entry.getKey() + ": " + entry.getValue()); } System.out.println("\nGrouped by Year:"); for (Map.Entry<Integer, List<LocalDate>> entry : groupedByYear.entrySet()) { System.out.println("Year " + entry.getKey() + ": " + entry.getValue()); } } } ``` 在这个例子中,我们给定了一个起始日期和一个结束日期,并使用`LocalDate`、`YearMonth`、`Integer`等作为键来将日期按不同的时间单位分组。最终的结果是四个`Map`,分别表示按、天、和年分组的日期列表。 运行以上代码,输出将会是: ``` Grouped by Month: 2022-01: [2022-01-01, 2022-01-02, ..., 2022-01-31] 2022-02: [2022-02-01, 2022-02-02, ..., 2022-02-28] ... 2022-12: [2022-12-01, 2022-12-02, ..., 2022-12-31] Grouped by Day: 2022-01-01: [2022-01-01] 2022-01-02: [2022-01-02] ... 2022-12-31: [2022-12-31] Grouped by Week: Week 1: [2022-01-01, 2022-01-02, ..., 2022-01-07] Week 2: [2022-01-08, 2022-01-09, ..., 2022-01-14] ... Week 52: [2022-12-24, 2022-12-25, ..., 2022-12-31] Grouped by Year: Year 2022: [2022-01-01, 2022-01-02, ..., 2022-12-31] ``` 这样,你就成功地按、天和年分组了。你可以根据自己的需求对起始日期、结束日期和输出格式进行调整。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值