TERADATA SQL学习随笔<一>

此博客内容简介及目录

http://www.cnblogs.com/weibaar/p/6644261.html

 

最近在TERADATA环境学习SQL。在这里记录一下学习中查过的知识点，作为备案。

目录：

1. 关于SQL学习及所用在线数据库
2. 表联合 （join）
3. SQL子查询
4. 在select时创建新字段 (as, case when)
5. 数据分组 （group by + 聚合函数count, sum, avg等）
6. 利用over (partition by)进行数据分组并创建新字段
7. 样本选择

 

1、关于SQL学习及所用在线数据库

之前有看过一些SQL学习的书。但如果从学习效率来说，跟着书学习SQL，不如直接看生产环境的工作代码，遇到语句不懂时搜索引擎查找相应语句，效率会更高（例如本文就是此类的集中体现）。

当然，除了零星的知识点以外，网上还存在一些在线练习SQL取数的网站，在这里列举如下：

1）基础SQL测试： http://www.w3school.com.cn/quiz/quiz.asp?quiz=sql

　　　　　　　　   http://sqlzoo.net/

2）基本语法练习-CodeCamdy： https://www.codecademy.com/zh/learn/learn-sql

3）在线直连数据库练习 http://www.sqlcourse.com

　　　　　　　　　　　https://livesql.oracle.com/apex/livesql/file/index.html

　　　　　　　　　　　http://www.sql-ex.ru/

当然，我们也可以去下一些基础练习数据库，安装mySql等进行练习。不过一般入门，学语法比学怎么装数据库更重要。见仁见智吧。

另外，相比于简单易懂的SQL语法学习，如何优化SQL语句效率，如何理解数据库结构等等更加重要。这也是下一步我学习的重点。 具体参考这个链接 https://www.zhihu.com/question/20116482

 

那么接下来，我选用oracle数据库来练习最近查阅的知识点。

在这个网站注册　https://livesql.oracle.com/apex/livesql/file/index.html 后，在code library选择【EMP and DEPT 】数据源导入，会在我们自己账户里创建以下表： dept, emp。以下代码均以此为实例改写。下图为两个表概览

 

2、表联合

select job,loc,dname from emp, dept where emp.deptno=dept.deptno

select job,loc,dname from emp join dept on dept.deptno = emp.deptno

 如上，上面两个表都是用deptno联合的，但一个是选择了所有表，再筛选，另一个是做一个join

最常用的还是join

 

3、SQL的子查询

select  loc, dname from
 (select job,loc,dname from emp 
        join dept on dept.deptno = emp.deptno
        where loc = 'DALLAS' OR loc = 'New York'    )

查询套查询，在实际工作环境中挺常见的。不是很难，就是用（）把子查询括起来。

可以把代码拷贝到如notepad++等代码编辑器里，看到具体查询嵌套关系，再一层一层反推取数逻辑。

 

4、在select里创建新字段

select创建新字段，可以直接用...as 创建字段

select ename, loc, 'DALLAS & NY' as city from emp 
        join dept on dept.deptno = emp.deptno
        where loc = 'DALLAS' OR loc = 'New York'

同理，也可以用case when创建有条件判断的字段，只需要加上括号即可。这个貌似还挺常见的

select ename, job, 
(case when sal >2000 then 'high'
                        when sal <1000 then 'low'
                        else 'middle'
                    End) as salary_level
    from emp

当然，case when还可以用于分组统计时创建字段，与group by联合使用。

---按job统计薪水大于1500的有多少人
select job, 
sum(case when sal >1500 then 1
                        
                        else 0
                    End) as salary_gt1500,
count(*) as people_vol
    from emp
    group by job

如要具体了解case when其他用法，还可再查看以下博文：

http://blog.sina.com.cn/s/blog_4c538f6c01012mzt.html

http://www.cnblogs.com/cyrix/articles/1750184.html

 

5、数据分组并统计

一般数据分组用group by分组，基本与聚合函数配合使用。

原则是select后面的所有列，如果没有用聚合函数，那么必须在group by里重写一遍。

举例如下面两段代码，第一段会报错

---错误：使用聚合函数count但没有group by，或只group by一列
select mgr, job, count(*) as people_vol from emp
group by mgr
---正确：group by后引用完整列
select mgr, job, count(*) as people_vol from emp
group by mgr, job

具体可参阅以下博文： http://www.cnblogs.com/gaiyang/archive/2011/04/01/2002452.html

 

6、利用over (partition by)分组并算相应值

之前的group by主要用于分组统计，而如sum, count等则是与group by组合输出同组的一行数据。

如果我们要对每一行数据都输出统计量，我们可以用over (partition by)进行分组并输出。

这个可以用于：分组排序，或分组聚合等等。

举例可看以下代码

---按job分组并给每个emp排序
select job, ename, sal,
row_number() over (Partition by job order by sal desc) as ranking
from emp
---分组并按job求各组平均值
select job, ename, sal,
avg(sal) over (Partition by job) as average_salary
from emp

具体用法，建议参阅以下博客：

http://www.cnblogs.com/lanzi/archive/2010/10/26/1861338.html

http://www.cnblogs.com/fxgachiever/archive/2010/09/15/1826792.html

 

7、数据分组并筛选：where与having

用where进行数据筛选在SQL里最常见。其工作环境中，主要嵌套子查询、或者多条件（and or联合）使用。注意结构就可以。

另外还有一种数据筛选是在分组以后进行，即group by .... having.... 之所以引用having，是因为where语句无法对聚合函数进行筛选

典型示例如下：

SELECT A COUNT(B) FROM TABLE GROUP BY A HAVING COUNT(B)>2

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

——引用自 http://www.cnblogs.com/gaiyang/archive/2011/04/01/2002452.html

我们可以用以下示例尝试

---按mgr, job分组，并选出职工数>1的组 
---（由于select是sql运行最后一步，比group by晚执行。所以having里不能直接用empl_vol）
select mgr, job, count(*) as empl_vol
from emp
group by mgr, job
having count(*)  > 1

 

8、数据分组并排序并筛选选出最近一批的数据

 

Group BY XXXX
HAVING
SUM(filteraaa) > 3 
QUALIFY RANK () OVER(PARTITION BY aa_ID ORDER BY MONTH_ID) =1;

 

Qualify rank 优化。报表执行顺序，确保每一步过滤掉足够多的信息

 

http://blog.sina.com.cn/s/blog_4d281a0301016jw2.html

 

http://community.teradata.com/t5/Database/qualify-rank-over-partition-question/td-p/47965

 

http://blog.sina.com.cn/s/blog_62d120530101h7vi.html

 

关于QUALIFY RANK() over组合应用

 

查阅搜索引擎发现，qualify rank() over用法似乎是teradata独特的用法之一。

类似于之前查阅的row_number() over….只不过这里qualify …可以直接=1或=2获得首位排序账户

 

sql 语句执行顺序

http://www.cnblogs.com/summer_adai/archive/2011/10/28/2227605.html

 

补充

show select * from TABLEAAA

可以批量看有哪些字段