2020-01-09 学习sql

1.按照日期查询不重复的id计数，求和                                                                                                                                         
select DATA_DT,count (distinct id),sum (SY),sum (RY) from P_DETAIL  group by DATA_DT
2.   NVL函数是一个空值转换函数 NVL（ comm,0);  preceding  代表前置
unbounded preceding and 1 preceding  ----代表无前置到自己的前一个  
last_value 这个最后的值  unbounded preceding and 1 preceding  是
SELECT a.id,a.data_dt,a.DKDYE,  a.DKDYE-a.HJ as c ,nvl(last_value(a.DKDYE) over(partition by  a.id order by a.DATA_DT rows between unbounded preceding and 1 preceding),0) as lastDKDYE FROM P_LCZB a
3.
select month,
  2         sum(tot_sales) month_sales,
  3         sum(sum(tot_sales)) over(order by month
  4           rows between unbounded preceding and current row) current_total_sales
  5    from orders
  6   group by month;