17,086
社区成员
发帖
与我相关
我的任务
分享
select count(distinct main_domain) from spd_domain t where t.is_chinese_site = 0 and t.alexa_ranking =-1
结果 1635t条记录
select count(*)
from (select DOMAIN,
DOMAIN_SOURCE,
MAIN_DOMAIN,
RECORD_DATE,
STATUS,
ALEXA_RANKING,
IS_CHINESE_SITE,
STATUS_DATE,
row_number() over(partition by MAIN_DOMAIN order by MAIN_DOMAIN) rn
from SPD_DOMAIN
where ALEXA = 0) t
where rn = 1
and t.IS_CHINESE_SITE = 0
and t.alexa_ranking = -1
理想情况下这两种查询返回记录总数是一样的
请看以下分析:
数据1(理想数据,第一条记录符合条件)
main_domain is_chinese_site alexa_ranking ALEXA
test.com 0 -1 0
test.com 1 -1 0
test.com 0 -1 1
数据2(非理想数据,第二条记录符合条件)
main_domain is_chinese_site alexa_ranking ALEXA
test.com 1 -1 0
test.com 0 -1 0
test.com 0 -1 1
执行 row_number() over(partition by main_domain order by MAIN_DOMAIN) 之后返回
数据1
main_domain is_chinese_site alexa_ranking ALEXA rn
test.com 0 -1 0 1
test.com 1 -1 0 2
test.com 0 -1 1 3
数据2
main_domain is_chinese_site alexa_ranking ALEXA rn
test.com 1 -1 0 1
test.com 0 -1 0 2
test.com 0 -1 1 3
查询
select count(*)
from (select row_number() over(partition by main_domain order by MAIN_DOMAIN) rn from SPD_DOMAIN) tmp
where rn=1 and IS_CHINESE_SITE = 0 and alexa_ranking = -1 and ALEXA = 0 时,数据1有记录,数据2无记录。
而查询
select count(distinct main_domain) from spd_domain t where t.is_chinese_site = 0 and t.alexa_ranking =-1 and ALEXA=0
数据1,数据2都有记录。
修改如下:
;with tmp
(
select * from SPD_DOMAIN where is_chinese_site = 0 and alexa_ranking =-1 and ALEXA=0
)
select count(*)
from (
select row_number() over(partition by main_domain order by MAIN_DOMAIN) rn from tmp
) tmp
where rn=1
lz,
理想情况下你那两种查询方式返回记录总数是一样的
请看以下分析:
数据1(理想数据,第一条记录符合条件)
main_domain is_chinese_site alexa_ranking ALEXA
test.com 0 -1 0
test.com 1 -1 0
test.com 0 -1 1
数据2(非理想数据,第二条记录符合条件)
main_domain is_chinese_site alexa_ranking ALEXA
test.com 1 -1 0
test.com 0 -1 0
test.com 0 -1 1
执行 row_number() over(partition by main_domain order by MAIN_DOMAIN) 之后返回
数据1
main_domain is_chinese_site alexa_ranking ALEXA rn
test.com 0 -1 0 1
test.com 1 -1 0 2
test.com 0 -1 1 3
数据2
main_domain is_chinese_site alexa_ranking ALEXA rn
test.com 1 -1 0 1
test.com 0 -1 0 2
test.com 0 -1 1 3
查询
select count(*)
from (select row_number() over(partition by main_domain order by MAIN_DOMAIN) rn from SPD_DOMAIN) tmp
where rn=1 and IS_CHINESE_SITE = 0 and alexa_ranking = -1 and ALEXA = 0 时,数据1有记录,数据2无记录。
而查询
select count(distinct main_domain) from spd_domain t where t.is_chinese_site = 0 and t.alexa_ranking =-1 and ALEXA=0
数据1,数据2都有记录。
修改如下:
;with tmp
(
select * from SPD_DOMAIN where is_chinese_site = 0 and alexa_ranking =-1 and ALEXA=0
)
select count(*)
from (
select row_number() over(partition by main_domain order by MAIN_DOMAIN) rn from tmp
) tmp
where rn=1