微信号:shushuojun

介绍:数据分析师之家.旨在为数据人提供一个学习、分享、互帮互助的家园.

小明的 SQL 问题解决日志(1)

2017-09-13 17:56 小明


本系列仅为小明在写SQL过程中,由浅入深遇到的一些问题、以及最后解决方案。我知道这其中有些问题,高手在12岁的时候就已经知道答案了,小明可能比你们慢了一点。


本文解决的问题:


1、有条件计数

2、去重后左连接

3、自关联,每对只取一条




文本演示code,默认用 SAS SQL 来演示,因为大家可能对 SAS 还是比较熟悉一些,但有些语句 SAS SQL 不支持的,改用其他。


1、有条件计数


以 SAS 中 sashelp 自带的 Cars 数据为例。


【问题:想计算每个 Make 下面,engine size >=3 的占比情况】



我们知道,计算每个 Make 的个数用:

select Make, count(*) from cars;


但如果要计算满足条件的个数,就要用

sum( case when ... then 1 else 0 end) as new_name


比如这里计算每个 Make 中 engine size >=3 的个数和占比:



结果为:



2、去重后左连接


对两个表进行关联时,通常有左连接(left join)、右连接(right join)、全连接(full join)、内连接(inner join)。


【问题:table1 左连接 table2 时,想对 table2 的数据按照 id 去重再做连接】


如上图所示,id=a 有多条数据,连接时只想连一条,此时对 table2 做 distinct 是无法满足的:

  • distinct id,则 value2,value3的信息就丢了;

  • distinct id value2 value3 也不行,也为 value2 和 value3 是不重复的。


此时可以用 

row_number() over(partition by id order by value2) as r

的方式来去重。由于 SAS 的 sql 不支持这个语句,因此我们用阿里ODPS来演示。


select *, row_number() over(partition by id order by value2) as r from table2



partition by id 表示按照 id 分组,order by value2 表示分组后按照 value2 来排序,按照顺序依次给一个序号 r=1,2...,如果想去重,只要指定 r=1 即可。


(PS:这里 r 分配给组内每条数据的值一定是唯一的,即使 value2 有重复,r 也是唯一的,因此用于去重正好)


我们尝试去重后左关联:



结果:


3. 自关联,每对只取一条


如下图,


【问题:想对 table1 做自关联,但相同一对只出现一次,比如 a-b 有了,那么b-a 就不需要了】



只要在关联的时加上条件 where a.id<b.id 即可。



结果:


所有 code,可在后台回复 sql1 获取。




更多 SQL 连载、Python 连载、SAS 教程 请关注 数说工作室


【统计师的 Python 系列】连载

  • 第1天:谁来给我讲讲Python?

  • 第2天:再接着介绍一下Python呗

  • 第3天:Numpy你好

  • 第4天:欢迎光临Pandas

  • 第5天:Pandas,露两手

  • 第6天:数据合并

  • 第7天:数据清洗(1)

  • 第8天:数据清洗(2)文本处理

  • 第9天:正则表达式

  • 第10天:数据聚合

  • 第11天:class-类


【文本挖掘系列】连载

  • 1、文本相似度思想

  • 2、词频与余弦相似度算法

  • 3、TF-IDF 治啰嗦利器


【分类战车SVM】系列

  • 开题话

  • 线性分类

  • 最大间隔分类器

  • 拉格朗日对偶问题

  • 核函数

  • SMO算法

  • 用Python做SVM模型


SAS系列,包括 【SAS IML系列】、【SAS 正则表达式系列】、【SAS 基础系列】

金融数据挖掘系列、量化投资系列、生物大数据系列 等等更多干货......



 
数说工作室 更多文章 招聘 | 蚂蚁金服-风险决策中心 | 第二弹 程序员总工会:以后写代码要按行数收费?那我能写到马云破产! 回归模型的一个隐藏指标,你知道吗? Python 验证 | 巴菲特推崇的“指数定投”到底如何? 程序员的专属奢侈品,你有几个?
猜您喜欢 2016年度异步社区优秀作译者评选 JavaScriptCore在实际项目中的使用的坑 我给独立程序员的建议。 Spark 1.4 新特性概述 混淆矩阵(Confusion matrix)