SQL语句整理及窗口函数的介绍

---

一、DB2

VALUES trunc(sysdate,'mm')

VALUES to_char(trunc(sysdate,'yyyy'),'YYYY-MM-DD HH24:MI:SS')

VALUES to_char(add_months(trunc(sysdate,'yyyy'),-12),'YYYY-MM-DD HH24:MI:SS')

VALUES CURRENT date -1 month -day(CURRENT date -1 day) DAY

VALUES CURRENT date -day(CURRENT date) day

VALUES DAY(CURRENT date -1 DAY)

VALUES CURRENT DATE -1 months + 1 DAYS - DAY(CURRENT DATE) DAYS 

 VALUES CURRENT date -day(CURRENT date) DAY
 
 VALUES CURRENT date -1 month +1 day-day(CURRENT date) DAY
 
 VALUES TO_char(current timestamp - 1 MONTHS +1 day-(DAY (current timestamp))DAYS-(HOUR(CURRENT timestamp))HOURS-(MINUTE(CURRENT timestamp))MINUTE-(SECOND(CURRENT TIMESTAMP))seconds,'YYYY-MM-DD HH24:MI:SS')
 
 VALUES VARCHAR_FORMAT(sysdate,'YYYY-MM-DD HH24:MI:SS')
 
 VALUES to_char(sysdate-11 month,'YYYY-mm')--单取月份如‘01’或年月*******重要
 
 VALUES trunc(CURRENT date,'YYYY-MM-DD HH24:MI:SS')
 
 VALUES YEAR(CURRENT date -12 MONTHS +1 DAYS -day(CURRENT date)DAYS)
 
 RENAME TABLE OA__OFFICE_SX_MO TO OA_OFFICE_SX_MO
 
 VALUES substr(sysdate, 12, 5)
 
 VALUES substr(CURRENT timestamp, 12, 5)

 VALUES substr(CURRENT date-3 months,6,2)
 
VALUES  concat(YEAR(CURRENT date -12 MONTHS +1 DAYS -day(CURRENT date)DAYS),substr(CURRENT DATE -12 months + 1 DAYS - DAY(CURRENT DATE)DAYS,6,2))

VALUES '111'||'222' --其结果就是'111222'

--计算timestamp格式的时间差(直接减会报错)
 VALUES TIMESTAMPDIFF(8,(CURRENT timestamp - ( CURRENT timestamp - 1 DAY))*1.00)


--查询哪个表在哪个schema下边
SELECT * FROM syscat.tables WHERE tabname LIKE '%T_COST_OA%'
--修改表结构
ALTER TABLE TKMGT.IFOL_OFFICE_SX_MO ALTER COLUMN col_2 SET DATA type decimal(19,2)

--解锁表
CALL SYSPROC.ADMIN_CMD
('reorg table TKMGT.IFOL_OFFICE_SX_MO')

--修改表名
alter table tkmgt.FS_DEPARTMENT rename column en_us to parent_department_id;
CALL SYSPROC.ADMIN_CMD
('reorg table tkmgt.FS_DEPARTMENT');

VALUES add_months(trunc(sysdate,'yyyy'),-12)

VALUES year(CURRENT date)||

ALTER TABLE "SSTK"."MOBILE_JYGL_DW_XDJZ" 
ADD PRIMARY KEY ("M_CODE",snap_date);

--替换中间的逗号
values REPLACE('213,123', ',', '')

VALUES sysdate

二、MYSQL

1.求最大连续登录天数

1.1用ROW_NUMBER() 窗口函数 按UID分组，按date升序排序

    SELECT 
    UID,
    date,
    row_number() OVER(PARTITION BY UID order by date) as sort
    FROM t1

1.2升序排序后,用date-sort==起始时间来判断是否连续，即起始值是否相同

运用函数: DATEDIFF(datepart,startdate,enddate) 返回两个日期之间的时间

SELECT 
UID,
date_sub(date,sort) as login_group,
min(date) as start_date,
max(date) as end_date,
count(1) as continuous_days
FROM (
    -- 第一段首先根据用户分组，登陆时间排序，结果按照登陆时间升序排列
    SELECT 
    UID,
    date,
    row_number() OVER(PARTITION BY UID order by date) as sort
    FROM t1
) a
GROUP BY UID,date_sub(date,sort)

结果分析: login_group为起始时间,
continuous_days是按uid和login_group分组后的行数,(可能会有重复值则计算不准确需要)
或者使用DATEDIFF(day,min(date),max(date)) 这个函数来返回两个日期之间的时间

2.窗口函数

窗口函数可以进行排序，生成序列号等一般的聚合函数无法实现的高级操作。
窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。
窗口函数也称为OLAP函数，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。
格式 <计算方式>+over+ (计算范围和排序依据)
窗口函数语法：其中[]中的内容可以省略

<窗口函数> over ([partition by <列清单>]
                        order by <排序用列清单>)

窗口函数大体可以分为以下两种：
1.能够作为窗口函数的聚合函数（sum，avg，count，max，min）
2.rank，dense_rank。row_number等专用窗口函数。

专用函数的种类：
1.rank函数：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。
2.dense_rank函数：同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。
3.row_number函数：赋予唯一的连续位次。即存在相同的位次也会分发给不同的排序号

2.1计算移动平均数

指定框架：根据所选列的某几行来进行计算
？ preceding：在所选行之前？行
？folloing：在所选行之后？行
范围包含所在行

select product_id, product_name, sale_price,
       avg(sale_price) over (order by product_id
	                          rows between 1 preceding and 1 following) as moving_avg--使用between规划范围，语句意思为rows 1 preceding
																				     --到rows 1 following
from Product;

该例子选取所在行及上下一行共三行做平均数

2.2lag和lead函数的使用

lag(co1,number(偏移行数),number(第一行),number(超出最上面边界的默认值))
查询我们得到的结果集上偏移相应行数的相应的结果。
lead(co1,number(偏移行数),number(第一行),number(超出最上面边界的默认值))
查询我们得到的结果集下偏移相应行数的相应的结果。
也可以用关联两个相同的表来替换这两个窗口函数

---