kibana 统计数量_常用的数据统计Sql 总结

1. 统计各个条件下的数据

selectBatchId,sum(CardSum) 总金额,sum(case when Status=1 then CardSum else 0 end) as 已使用,sum(case when Status=2 then CardSum else 0 end) as 已冻结 from GiftCard group by BatchId

2. 统计每日,每月,每年的数据

select year(AddTime) 年,month(AddTime) 月,day(AddTime) 日,COUNT(1) 数量,sum(CardSum) 销售合计from GiftCardgroup by year(AddTime),month(AddTime),day(AddTime)

3. 某列去重统计

select COUNT(BatchId),COUNT(distinct BatchId),COUNT(distinct BatchName)from GiftCard

4. 行转列

SELECT *FROM ( SELECT  BatchName,  CardSum as TotAmount  FROM GiftCard) as sPIVOT(  SUM(TotAmount) FOR BatchName IN (zx测试商品, test新人优惠券,测试高考大放送))AS MyPivot

5. 得到表中最小的未使用的ID号

SELECT (CASE WHEN EXISTS(SELECT * FROM GiftCard b WHERE b.Id = 1) THEN MIN(Id) + 1 ELSE 1 END) as Id FROM GiftCardWHERE NOT Id IN (SELECT a.Id - 1 FROM GiftCard a)

6. 查询某一列数据不重复的数量

select * from GiftCard a where not exists(select 1 from GiftCard where BatchName=a.BatchName and ID

7. 按年统计1月到12个月的销量

select year(AddTime) as '年',SUM(case when MONTH(AddTime)=1 then CardSum else 0 end ) as '一月',SUM(case when MONTH(AddTime)=2 then CardSum else 0 end ) as '二月',SUM(case when MONTH(AddTime)=3 then CardSum else 0 end ) as '三月',SUM(case when MONTH(AddTime)=4 then CardSum else 0 end ) as '四月',SUM(case when MONTH(AddTime)=5 then CardSum else 0 end ) as '五月',SUM(case when MONTH(AddTime)=6 then CardSum else 0 end ) as '六月',SUM(case when MONTH(AddTime)=7 then CardSum else 0 end ) as '七月',SUM(case when MONTH(AddTime)=8 then CardSum else 0 end ) as '八月',SUM(case when MONTH(AddTime)=9 then CardSum else 0 end ) as '九月',SUM(case when MONTH(AddTime)=10 then CardSum else 0 end ) as '十月',SUM(case when MONTH(AddTime)=11 then CardSum else 0 end ) as '十一月',SUM(case when MONTH(AddTime)=12 then CardSum else 0 end ) as '十二月'from GiftCardgroup by year(AddTime)
19812eb0e29fa7f9cf3efd6d298fcd4b.png
  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值