今天上午处理一个SQL语句的问题,情况是,有一个clicks表,以及geoip和geoip_location表。
clicks表中存取的是客户的点击数,geoip和geoip_location分别存储了各个国家的ip网段和地址信息。
通过如下SQL可以看出每一个ip符合条件的点击数
select count(click_ip) as count,click_ip from
fb_fy_clicks
where earn_id = 191 AND if_pay='t' AND date(crdate)='2007-12-24'
group by click_ip;
下面通过ip找到所在的国家
select * from
(select count(click_ip) as count,click_ip from fb_fy_clicks
where earn_id = 191 AND if_pay='t' AND date(crdate)='2007-12-24'
group by click_ip) as tb
left join geoip G
on ip4(tb.click_ip) <<= G.network
left join geoip_location as geoip
on G.location_id = geoip.id
这样虽然说通过ip找到国家了,但是有的ip是别人通过代理或者伪装的ip,所以有多条ip的重复记录,尽管这些只是少数,但是我们需要把这些对应多个国家的ip按照1个来算。如果仅仅使用distinct我们无法过滤掉多出来的国家。正好postgresql有distinct on
文档记载:
我们还可以用任意表达式来判断什么行可以认为是独立的:
SELECT DISTINCT ON (expression [, expression ...]) select_list ...
这里 expression 是任意值表达式, 它为所有行计算。如果一个行集合里所有行计算出的该表达式的值是一样的, 那么我们认为它们是重复的并且因此只有第一行保留在输出中。 请注意这里的一个集合的"第一行"是不可预料的, 除非你在足够多的字段上对该查询排了序,保证到达DISTINCT过滤器的行的顺序是唯一的。 (DISTINCT ON处理是发生在ORDER BY排序后面的。)
DISTINCT ON子句不是 SQL 标准的一部分, 有时候有人认为它是一个糟糕的风格,因为它的结果是不可判定的。 如果用有选择的GROUP BY和在FROM中的子查询,那么我们可以避免使用这个构造, 但是通常它是更方便的候选方法。
正好符合需求:
将SQL改为:
select distinct on (click_ip) click_ip,network,country,count from (
(select count(click_ip) as count,click_ip from fb_fy_clicks
where earn_id = 191 AND if_pay='t' AND date_trunc('day',crdate)='2007-12-24'
group by click_ip) as tb left join geoip G
on ip4(tb.click_ip) <<= G.network
left join geoip_location as geoip
on G.location_id = geoip.id) as tmp
还有一种情况就是,有些client_ip重复的记录不需要被过滤掉,比如一些client_ip重复的记录,但是他的data_platform字段是不一样的,这样的记录是不应该被过滤掉的。这时,我们使用
SELECT DISTINCT ON (client_ip,data_platform) client_ip,SUM(impressions) AS impressions,crdate,data_platform from views a,ads b
WHERE a.ad_id = b.id
GROUP BY client_ip,data_platform,crdate
真实例子:
SELECT ad_id,country,SUM(impressions) AS impressions,dates,source FROM
(SELECT DISTINCT ON (client_ip,ad_id,source) client_ip,ad_id,COALESCE(country,'OTHER') AS country,impressions,dates,source FROM
(((SELECT DATE_TRUNC('DAY',a.crdate) AS dates,b.ad_id,a.client_ip,COUNT(a.fb_uid) AS impressions, 0 AS source FROM views_20081020 a,ads b WHERE a.ad_id = b.id AND a.fb_uid IS NOT NULL GROUP BY b.ad_id,a.client_ip,dates ORDER BY b.ad_id asc)
UNION ALL
(SELECT DATE_TRUNC('DAY',a.crdate) AS dates,b.ad_id,a.client_ip,COUNT(a.bebo_uid) AS impressions, 1 AS source FROM views_20081020 a,ads b WHERE a.ad_id = b.id AND a.bebo_uid IS NOT NULL GROUP BY b.ad_id,a.client_ip,dates ORDER BY b.ad_id asc )UNION ALL
(SELECT DATE_TRUNC('DAY',a.crdate) AS dates,b.ad_id,a.client_ip,COUNT(a.ms_uid) AS impressions, 2 AS source FROM views_20081020 a,ads b WHERE a.ad_id = b.id AND a.ms_uid IS NOT NULL GROUP BY b.ad_id,a.client_ip,dates ORDER BY b.ad_id asc )
UNION ALL
(SELECT DATE_TRUNC('DAY',a.crdate) AS dates,b.ad_id,a.client_ip,COUNT(a.hi5_uid) AS impressions, 3 AS source FROM views_20081020 a,ads b WHERE a.ad_id = b.id AND a.hi5_uid IS NOT NULL GROUP BY b.ad_id,a.client_ip,dates ORDER BY b.ad_id asc)) AS tmp_ads_imprs_country_stats
LEFT JOIN geoip G ON ip4(tmp_ads_imprs_country_stats.client_ip) <<= G.network LEFT JOIN geoip_location AS geoip_loc ON G.location_id = geoip_loc.id ) AS tmp_ads_imprs_country_stats_t ) AS FOO GROUP BY ad_id,country,dates,source ORDER BY ad_id ASC;
分享到:
相关推荐
to address the process of deploying a PostgreSQL database on the cloud, we include a detailed architecture of PostgreSQL in one of the chapters. The architecture of PostgreSQL should help you ...
Beginning PostgreSQL on the Cloud Simplifying Database as a Service on Cloud Platforms 英文无水印原版pdf pdf所有页面使用FoxitReader、PDF-XChangeViewer、SumatraPDF和Firefox测试都可以打开 本资源转载...
Mastering PostgreSQL 10 Expert techniques on PostgreSQL 10 development and administration 英文epub 本资源转载自网络,如有侵权,请联系上传者或csdn删除 查看此书详细信息请在美国亚马逊官网搜索此书
The easiest way to get started with PostgreSQL on the Mac 一键启动的PostgreSQL Server on the Mac
总计COUNT_DISTINCT 此扩展提供了COUNT(DISTINCT ...)的替代方法,该方法对于大量数据通常会以排序和不良性能而告终。 职能 有两个多态聚合函数,用于处理按值传递的固定长度数据类型(即,在64位计算机上最多为...
Migrate PostgreSQL to the cloud Choose the best configuration and specifications of cloud instances Set up a backup strategy that enables point-in-time recovery Use connection pooling and load ...
postgresql PGCA 课程PPT01_postgresql_PGCA_PGCP_PGCM_课程全免费 postgresql PGCA 课程PPT01_postgresql_PGCA_PGCP_PGCM_课程全免费 postgresql PGCA 课程PPT01_postgresql_PGCA_PGCP_PGCM_课程全免费 postgresql ...
PostgreSQL(postgresql-13.5.tar.bz2) PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的对象关系型数据库管理系统。...
PostgreSQL中文学习手册 PostgreSQL PostgreSQL PostgreSQL学习手册 学习手册 学习手册 (数据表 数据表 ) 4 一、表的定义: 一、表的定义: 一、表的定义: . 4 PostgreSQL PostgreSQL PostgreSQL学习手册 学习手册...
Navicat for PostgreSQL是一套专为PostgreSQL设计的强大数据库管理及开发工具。它可以用于任何版本 7.5 或以上的 PostgreSQL 数据库服务器,并支持大部份 PostgreSQL最新版本的功能,包括触发器、函数、管理用户等。...
postgresql 安装程序。这是个开源的数据库管理程序。
Manage open source PostgreSQL versions 10 on various platforms. Explore best practices for planning and designing live databases Select and implement robust backup and recovery techniques in ...
postgresql
PostgreSQL 高可用解决方案patroni 演讲PPT.
postgresql-42.5.0.jar是Java上的一个驱动程序,用于连接PostgreSQL数据库并与其进行交互。它可以让Java程序员方便地使用PostgreSQL数据库,并提供了许多功能和工具,使程序员可以编写高效、稳定和高性能的应用程序...
最新版本PostgreSQL JDBC驱动包,还包括老版本驱动包: postgresql-8.4-703.jdbc4(支持PostgreSQL 8) postgresql-9.4.1212(支持PostgreSQL 9) postgresql-42.2.10(支持PostgreSQL 42)
Postgresql
PostgreSQL(postgresql-14.2-2-windows-x64.exe),适用于Windows系统:PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系开发的POSTGRES,4.2版本为基础的对象...
PostgreSQL(postgresql13-devel-13.5-1PGDG.rhel7.x86_64.rpm)适用于RHEL/CentOS/Oracle Linux 7 - x86_64 PostgreSQL是一种特性非常齐全的自由软件的对象-关系型数据库管理系统(ORDBMS),是以加州大学计算机系...