oracle 在...之上计数(不同的)范围函数)

dfty9e19 于 2023-06-05 发布在 Oracle

关注(0)|答案(2)|浏览(365)

我想在mm（日期）上计算不同的yyyydd。（+-2天）但是，distinct函数不能与over一起使用。
如果我删除distinct，它会给予我yyyydd的总数，但yyyydd可能有很多重复。这就是为什么我想添加distinct。它与count(distinct) over (partition by... doesn't work in Oracle SQL有点相似，但不同：（

with tbl1 as 
(select '20230321' yyyymmdd from dual union all
select '20230327' yyyymmdd  from dual union all
select '20230422' yyyymmdd from dual union all
select '20230423' yyyymmdd from dual union all
select '20230501' yyyymmdd from dual union all
select '20230502' yyyymmdd  from dual union all
select '20230507' yyyymmdd from dual union all
select '20230521' yyyymmdd from dual union all
select '20230523' yyyymmdd from dual union all
select '20230527' yyyymmdd from dual union all)
,
tbl2 as(
select a.*, substr(yyyymmdd,1,6) as yyyymm, substr(yyyymmdd ,7,9) as mm
from tbl1 a)

select
b.*
, count(1) over (order by to_number(mm) range between 2 preceding and 2 following) as cnt
, count(distinct yyyymm) over (order by to_number(mm) range between 2 preceding and 2 following) as cnt -- error
from tbl2 b

| yyyyddmm|毫米|cnt（错误）|cnt（wanted）|
| - -----|- -----|- -----|- -----|
| 20230501| 01| 2| 1|
| 20230502| 02| 2| 1|
| 20230507| 07| 1| 1|
| 20230321|二十一|5个|3|
| 20230521|二十一|5个|3|
| 20230422|二十二|5个|3|
| 20230423|二十三|5个|3|
| 20230523|二十三|5个|3|
| 20230527|二十七|2| 2|
| 20230327|二十七|2| 2|

oracle

来源：https://stackoverflow.com/questions/76380289/countdistinct-over-partition-by-range-function

2条答案

按热度按时间

nfs0ujit1#

您可以使用model子句来模拟窗口函数中不允许的操作。

with tbl1 as (
  select '20230321' yyyymmdd from dual union all
  select '20230327' yyyymmdd  from dual union all
  select '20230422' yyyymmdd from dual union all
  select '20230423' yyyymmdd from dual union all
  select '20230501' yyyymmdd from dual union all
  select '20230502' yyyymmdd  from dual union all
  select '20230507' yyyymmdd from dual union all
  select '20230521' yyyymmdd from dual union all
  select '20230523' yyyymmdd from dual union all
  select '20230527' yyyymmdd from dual
)

select *
from tbl1 b
model
  /*To turn off uniqueness check of dimension values*/
  unique single reference
  /*What should be used for offsets*/
  dimension by (substr(yyyymmdd ,7,9) as mm)
  measures (
    /*To include in the output*/
    yyyymmdd,
    /*To allow reference in the right side of RULES*/
    substr(yyyymmdd,1,6) as yyyymm,
    /*Counter for DISTINCT*/
    0 as cnt
  )
  rules update (
      cnt[any] = count(distinct yyyymm)[mm between cv(mm) - 2 and cv(mm) + 2]
  )
order by 1, yyyymmdd

| MM| YYYYMMDD| YYYYMM| CNT|
| - -----|- -----|- -----|- -----|
| 01| 20230501| 202305| 1|
| 02| 20230502| 202305| 1|
| 07| 20230507| 202305| 1|
| 二十一|20230321| 202303| 3|
| 二十一|20230521| 202305| 3|
| 二十二|20230422| 202304| 3|
| 二十三|20230423| 202304| 3|
| 二十三|20230523| 202305| 3|
| 二十七|20230327| 202303| 2|
| 二十七|20230527| 202305| 2|
fiddle

赞(0）回复(0）举报 2023-06-05

polhcujo2#

如果不允许distinct在解析函数中包含order by，则使用子查询：

with tbl1 as (
  select '20230321' yyyymmdd  from dual union all
  select '20230327' yyyymmdd  from dual union all
  select '20230422' yyyymmdd  from dual union all
  select '20230423' yyyymmdd  from dual union all
  select '20230501' yyyymmdd  from dual union all
  select '20230502' yyyymmdd  from dual union all
  select '20230507' yyyymmdd  from dual union all
  select '20230521' yyyymmdd  from dual union all
  select '20230523' yyyymmdd  from dual union all
  select '20230527' yyyymmdd  from dual ),

tbl2 as(
  select a.*, substr(yyyymmdd,1,6) as yyyymm, substr(yyyymmdd ,7,2) as dd 
  from tbl1 a)
select yyyymm, dd, 
       (select count(distinct yyyymm) 
       from tbl2 x where dd between b.dd - 2 and b.dd + 2 ) cnt
from tbl2 b order by dd

dbfiddle demo

赞(0）回复(0）举报 2023-06-05

我来回答

oracle 在...之上计数(不同的)范围函数)

2条答案

相关问题

热门标签

最新问答