count(distinct) over（按...范围函数分区）

我想计算不同的yyyydd超过mm（日期）。 (+- 2 天) 但是，distinct 函数不能与 over 一起使用。

如果我删除不同的，它会给我总计数yyyydd, but yyyydd可以有很多重复的。这就是为什么我想添加不同的。 这有点类似于count(distinct) over (partition by... 在 Oracle SQL 中不起作用 https://stackoverflow.com/questions/55347200/countdistinct-over-partition-by-doesnt-work-in-oracle-sql但不同:(

with tbl1 as 
(select 'tay' cst_name, 'toy1' product_name, '20230501' yyyymmdd from dual union all
select 'tay' cst_name, 'toy1' product_name, '20230502' yyyymmdd  from dual union all
select 'tay' cst_name, 'toy1' product_name, '20230507' yyyymmdd from dual union all
select 'ray' cst_name, 'toy2' product_name, '20230321' yyyymmdd from dual union all
select 'ray' cst_name, 'toy2' product_name, '20230421' yyyymmdd from dual union all
select 'ray' cst_name, 'toy2' product_name, '20230422' yyyymmdd  from dual union all
select 'ray' cst_name, 'toy1' product_name, '20230423' yyyymmdd from dual union all
select 'ray' cst_name, 'toy1' product_name, '20230423' yyyymmdd from dual union all
select 'ray' cst_name, 'toy1' product_name, '20230527' yyyymmdd from dual union all
select 'ray' cst_name, 'toy2' product_name, '20230527' yyyymmdd from dual )
,
tbl2 as(
select a.*, substr(yyyymmdd,1,6) as yyyymm, substr(yyyymmdd ,7,9) as mm
from tbl1 a)

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

您可以使用model https://docs.oracle.com/en/database/oracle/oracle-database/19/dwhsg/sql-modeling-data-warehouses.html子句来模拟窗口函数，其中包含不允许的操作。

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

fiddle https://dbfiddle.uk/N8cAFUyl