KeepNotes blog

Stay hungry, Stay Foolish.

0%

初学SQL（三）

Posted on Edited on In ,
Symbols count in article: 3.4k Reading time ≈ 3 mins.

T-SQL作为SQL的扩展语言，是SQL程序设计语言的增强版，它是用来让应用程序与SQL Server沟通的主要语言

SQL作为结构化查询语言，是标准的关系型数据库通用的标准语言；T-SQL是在SQL基础上扩展的SQL Server中使用的语言 T-SQL的一些用法还是跟SQL有区别的。。。还好SQL Server是包含了T-SQL和SQL的语法，所以对于SQL Server来说其实都可以~

通过DataCamp数据分析在线学习平台中Intermediate SQL Server教程的学习T-SQL

看看微软SQL-SERVER文档更好，有中文版，非常详细https://docs.microsoft.com/zh-cn/sql/t-sql/language-reference?view=sql-server-2017

下面只是一些简单的记录，以后用到了再翻文档。。。

Imputing missing values

用ISNULL()函数不仅可以用于判断变量是否为NULL，还可以替换NULL，如查询表中VAL小于20的值，包括NULL值：

select * from Table where ISNULL(VAL,-1) < 20
## 等于：
select * from Table where VAL IS NULL or VAL < 20

ISNULL()函数还可以用其他列的值代替某个列中的空值：

select IncidentState, ISNULL(IncidentState,City) as Location
from Incidents
where IncidentState is NULL

COALESCE()函数可以接受一系列的值，如果所有项都为NULL，那么只使用一个值，并返回第一个非空值

select Country, COALESCE(IncidentState, City, Country) as Location
from Incidents
shere Country IS NULL

CASE statements

CASE语句也是条件判断的一种，使用Case语句可以实现比较复杂的if语句

SELECT DurationSeconds, 
      CASE WHEN (DurationSeconds <= 120) THEN 1
       WHEN (DurationSeconds > 120 AND DurationSeconds <= 600) THEN 2
       WHEN (DurationSeconds > 601 AND DurationSeconds <= 1200) THEN 3
       WHEN (DurationSeconds > 1201 AND DurationSeconds <= 5000) THEN 4
       ELSE 5 
       END AS SecondGroup   
FROM Incidents

Math Functions

DATEDIFF计算两个日期变量的时间的间隔，其中datepart参数对应不同时间间隔类型，比如yy是年的缩写，dd是日的缩写等等

SELECT OrderDate, ShipDate, 
       DATEDIFF(dd, OrderDate, ShipDate) AS Duration
FROM Shipments

DATEADD给某个日期增加时间间隔，Type对应不同时间间隔类型，如Day（缩写DD）是日，WEEK是周，YEAR是年等等

SELECT OrderDate, 
       DATEADD(DD,5,ShipDate) AS DeliveryDate
FROM Shipments

此外还有GETDATE用于获取系统时间

ROUND用于把数值舍入为指定的小数位数，比如四舍五入为最近的整数：

SELECT Cost, 
       ROUND(Cost,0) AS RoundedCost
FROM Shipments

ROUND如果指定了第三个参数function，如果其是个非0值（一般默认是0），则可以用于截断数值

SELECT Cost, 
       ROUND(Cost, 0, 1) AS TruncateCost
FROM Shipments

计算平方和平方根，另外常用的还有POWER,ABS,EXP等等

SELECT WeightValue, 
       SQUARE(WeightValue) AS WeightSquare, 
       SQRT(WeightValue) AS WeightSqrt
FROM Shipments

Processing Data in SQL Server

使用WHILE循环，比如累加@counter至小于30

DECLARE @counter INT 
SET @counter = 20

WHILE @counter < 30
BEGIN
    SELECT @counter = @counter + 1
END
SELECT @counter

派生表是在外部查询的FROM子句中定义的，只要外部查询一结束，派生表也就不存在了；派生表可以简化查询，避免使用临时表

派生表是一张虚表，在数据库中并不存在，是我们自己创建的，目的主要是为了缩小数据的查找范围，提高查询效率

SELECT a.RecordId, a.Age, a.BloodGlucoseRandom, 
       b.MaxGlucose
FROM Kidney a
join (SELECT Age, MAX(BloodGlucoseRandom) AS MaxGlucose FROM Kidney GROUP BY Age) b 
on a.Age = b.Age

CTE（Common table expression）是一个基于简单查询的临时结果集，在单个语句的执行范围内定义的临时结果集，只在查询期间有效；WITH后面接的是CTE name和column name，而AS则是包含了定义的表

WITH cte (MaxGlucose) 
AS (SELECT MAX(BloodGlucoseRandom) AS MaxGlucose FROM Kidney)

SELECT a.Age, b.MaxGlucose
FROM Kidney a
JOIN cte b on a.BloodGlucoseRandom = b.MaxGlucose

Window Functions

窗口函数（window function）是一个很好用的功能。。。

窗口函数是整个SQL语句最后被执行的部分，这意味着窗口函数是在SQL查询的结果集上进行的，因此不会受到Group By， Having，Where子句的影响

常用的有Over语句，与其一起使用的有：Partition By语句（类似于Group By），Order By语句（相应的其一般也跟着Row_Number()，Lead()，LAG()等函数使用），相关资料可以看：SQL Server 窗口函数以及SQL Server中的窗口函数

SELECT OrderID, TerritoryName, 
       SUM(OrderPrice) OVER (PARTITION BY TerritoryName) AS TotalPrice
FROM Orders

FIRST_VALUE和LAST_VALUE可以用于返回第一个和最后一个值

SELECT TerritoryName, OrderDate, 
       FIRST_VALUE(OrderDate) OVER(PARTITION BY TerritoryName ORDER BY OrderDate) AS FirstOrder
FROM Orders

LEAD和LAG函数可以用于返回数据集先前/之后行的数据

SELECT TerritoryName, OrderDate, 
       LAG(OrderDate) OVER(PARTITION BY TerritoryName ORDER BY OrderDate) AS PreviousOrder,
       LEAD(OrderDate) OVER(PARTITION BY TerritoryName ORDER BY OrderDate) AS NextOrder
FROM Orders

RANK函数可以用于返回PARTITION BY分组后每行的排名

SELECT TerritoryName, OrderDate, 
       RANK () OVER(PARTITION BY TerritoryName ORDER BY OrderDate) AS OrderCount
FROM Orders

ROW_NUMBER函数对输出进行编号

本文出自于http://www.bioinfo-scrounger.com转载请注明出处

Related Posts