SQL 小 tips

angnuan123

已于 2022-05-18 10:23:10 修改

阅读量130

点赞数

分类专栏： sql 文章标签： sql

于 2021-10-11 17:26:31 首次发布

本文链接：https://blog.youkuaiyun.com/angnuan123/article/details/120707537

版权

sql 专栏收录该内容

1 篇文章

订阅专栏

本文深入探讨了SQL中的窗口函数，如sum(), count()等，通过多个实例展示了如何利用OVER()子句进行透视分析。同时，对比了rank()和dense_rank()的区别，并介绍了try_convert()函数在处理数据转换错误时的用法。此外，还提到了text类型列在分组操作中的限制及其解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 开窗函数

2. rank() vs dense_rank()

3. try_convert

4. 提示报错

1. 开窗函数

开窗函数格式：函数名(列) OVER(选项)，实现的功能是通过over窗口进行透视。

函数名可以是常见的 sum(),count(),max() 等

ex1:
--结果行第三列数值一样，为满足COLC<2000 的行数
select colA,colB,count(*) over() from #table where colC<2000;

ex2:
--按照age排序，取第一行到目前行的最大colA
select age,colB,max(colA) over(order by age rows between unbounded preceding and current row) from #table where colC<2000;
--可以简化为：
select age,colB,max(colA) over(order by age) from #table where colC<2000;

ex3:
--结果是按照age排序，取小于目前行age的最大colA
select age,colB,max(colA) over(order by age range between unbounded preceding and current row) from #table where colC<2000; --不可以简化

ex4:
--结果是按照age排序，取目前行前一行及后3行colA的和
SELECT age,colB，
       SUM(colA) OVER(ORDER BY age ROWS BETWEEN 1 PRECEDING AND 3 FOLLOWING)  
  FROM #table;

ex5:
--算当前年龄排名
SELECT age,colB，
COUNT(*) OVER(ORDER BY age ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM #table;


ex6:
--算同龄人中最高身高
SELECT age,
MAX(height) OVER(PARTITION BY Age order by height) 同龄人最高工资
FROM #table;

2. rank() vs dense_rank()

rank:假设有相同排名，后面就会吞掉占用的位置：1,2,2,4,5,6

dense_rank() :假设有相同排名，后面不会吞掉占用的位置：1,2,2,3,4,5

3. try_convert

有时候将一列从A格式转化为B格式的时候会报错，如果不知道错误具体情况很难debug,这种类型错误经常出现在，cast，convert这两个函数中，可以使用try_convert() ,try_cast() 函数解决。

try_convert(type,colNAME)
try_cast(colNAME as type)

ex: select colA from tableA 
    where try_convert(float,colA) is null;

4. 提示报错

Columns of type text, ntext, and image cannot be used in group_by_expression

是因为text 类型的列不可以进行排序分组等操作，需要转为varchar 形式

CAST(email AS NVARCHAR(100))