sql-按条件统计非重复值,count(distinct case when)使用

背景

项目中,遇到一个统计需求,从某张表中按照条件分别统计。刚开始想到用union all的写法,太臃肿,后来使用count(distinct case when)解决此问题

count

数据统计中,count出现最频繁

最简单的用法

select count(*) from table where ....

select count(distinct xx) from table where ...

但最简单的用法也有其深刻的地方,比如这里其实有3种写法,count(1)、count(*)、count(字段),它们有什么区别呢?

  • count(1) 和 count(*)

count(1)和count(*)差别不大,使用count(*)时,sql会帮你自动优化,指定到最快的字段。所以推荐使用count(*)

  • count(*) 和 count(字段)

count(*)会统计所有行数,count(字段)不会统计null值

count(case when)

条件统计,即对某个table分条件统计,比如表test_db,有一个字段user_id(可能重复), gender(man、women),需要统计man和women的人数

可以使用where分别统计

select count(distinct user_id) as man_cnt from test_db where gender = 'man'

select count(distinct user_id) as women_cnt from test_db where gender = 'women'

也可以使用按条件统计

select count(distinct case gender = 'man' then user_id end) as man_cnt

, count(distinct case gender = 'women' then user_id end) as woman_cnt

from test_db

 

sql中!=与的区别

背景

sql中表示不等于关系时,有两种写法!=、<>,抱着刨根问底的精神,看看这两个符号到底有什么不同

标准答案

https://stackoverflow.com/questions/18015422/what-is-difference-between-and-in-sql-server

搬运过来

这两个符号没有任何区别,你使用哪个都一样。

Microsoft的文档中,这两个符号的意义相同,都是 Not Equal To

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/not-equal-to-transact-sql-exclamation?view=sql-server-2017

但,<>是ANSI 99 SQL标准中的定义,!=不是。所以,不是所有的DB引擎都支持!=。

最终推荐使用<>。

mysql-使用联合索引

背景

项目使用mysql数据库,某张表数据量2000W左右,比较大,在组合查询时发生超时,需要优化。

优化

查询优化常用方法就是建立索引

查询语句样例:查询在20181001-20181010时间范围内A=a且B=b的数据

select * from table where A = 'a' and B = 'b' and Date > '20181001' and Date < '20181010'

按照查询建立了一个组合索引 idx_query(A, B, Date),查询仍然报超时。然后开始研究索引原理,最后更改索引中列的顺序为idx_query(B,A,Date)解决。

---------------------------------------------------------------------------------------------

上面只是简单的项目背景,下面从原理细细解读索引

索引原理

首先,出问题以后再建立索引,是一种不好的习惯。排查、定位、解决问题会耗费很多开发时间和精力。

当然,不充分思考就给大多数列建立单列索引,给所有查询建立联合索引,是一种更坏的习惯。毕竟删除索引风险远高于添加索引。

使用索引需要成本:

  • 空间成本:索引是占空间的,大表的联合索引占用空间不可忽略
  • 时间成本:使用不对的索引会浪费时间
  • 更新成本:增删改操作只要跟索引有关系,就需要更新索引

索引适合在返回大表中很小一部分数据时使用,很小当然越小越好,5%以下还可以,1%以下更好,如果表的数据量非常大,千万级别,返回行数要小到0.1%以下

从很大表中用联合条件查询出很小的数据,等价于count(distinct 联合条件) 的值很大,这个值被称为Cardinality,索引适合度=Cardinality/表记录数。

索引误区

多个单列索引(如idx_A, idx_B)不等同于联合索引(idx_A_B)

两个联合索引里的列顺序不同,这两个联合索引也不等价,适用场景不同

联合索引idx_A_B能够覆盖的场景

  • A = ? and B = ?
  • A = ? and B in (?, ?, ?)
  • A = ?
  • A in (?,?,?)

覆盖场景可以根据索引存储结构(B+Tree)推导出。

上面罗列的几种覆盖场景,效率由上至下越来越低,因为扫描行数越来越大

联合条件中,最左侧的索引列很重要,最好使用Cardinality最大的列。

总结

项目中遇到的问题,最终根据索引原理中 最左侧索引列使用Cardinality最大列 解决。

使用java获取当前是星期几

有个小需求,获取今天是周几,想我大java,获取一个星期几不是手到擒来。

第一想法,System.currentTimeMillis(),这是个时间戳,还得处理秒数,太麻烦。

第二想法,new Date() deprecated?  好吧。

第三想法,Calendar,简单写下代码,开始调试

weekDay = 3,wtf?今天是周二,怎么给了我一个3,只听说过从0、1计数,没听说过从2开始计数的啊!

继续查

国际上是以星期日为一周第一天的开始,Calendar中提供的DAY_OF_WEEK获取的一周也是以星期日作为一周的开始。

而中国的习惯是以星期一作为一周的开始,所以需要根据项目的实际需求来设置。

好吧,真是人生处处皆学问,写代码还能学知识。

怎么办呢?Calendar有个api叫setFirstDayOfWeek,原来从哪一天开始是可以设置的,java也是蛮人性化的么~

继续调试

weekDay = 3

这就有点意思了,这个api是用来看的么? 继续查

官方文档一大堆,概括为:

setFirstDayOfWeek的方法意思只对WEEK_OF_MONTH WEEK_OF_YEAR 有作用.

WEEK_OF_MONTH :当前Calendar日期对象是当前月的第几周.

WEEK_OF_YEAR :当前Calendar 日期对象是当前年的第几周.

好吧,老老实实写下比较丑陋的代码

Hollow导读

本文大部分内容来源于Hollow官方文档

开发过程中会遇到这样的数据:体量算不上“大数据”,数据在变化,幅度也不大。处理这类数据的时候,一般是把数据放到内存中(容器、json、xml、RDBMS),隔断时间更新一次。

这样处理有很多局限性

  • 内存限制
  • 更新频率不能过快,造成延迟
  • 频繁的读写造成I/O、GC等

世界陷入水火,一般的套路现在主角就该出场了

Netflix推出了Hollow,它是数据的利剑,内存的盾牌,它将不JSON、不XML、少GC、高效率的解决问题,总而言之,他是人民的大救星,下面请一起走进科学,走进Hollow的内心世界。

Hollow致力于解决内存数据问题,处理的量级(将数据转为JSON)一般在GB级别,TB/PB就爱莫能助了。

快照-增量

生产的数据有两种类型,Snapshot、Delta,即全量、增量数据。大多数情况下,我们处理的是增量数据。

生产-消费者模式

一个生产者服务多个消费者,生产者生产快照和增量数据更新至BLOB(二进制大数据)文件,消费者在内存中使用数据,只读属性保证了消费的高效。生产文件到内存对开发者是透明的。

数据模型

数据模型基于一个POJO,又相当于数据库的一行。开发者只需要定义一个POJO,Hollow的API-Generator会生产这个POJO相应的消费文件。

它适用于只读数据、单个生产者、可能多个消费者的情形。Hollow实现持久化的唯一机制是利用BLOB存储,它只是一个文件存储,可能是S3、NFS、甚至一台FTP服务器。启动的时候,消费者们读取整个数据集的快照,并将数据集引导到内存中。通过增量的方法,可以保证内存中的数据集是最新的。对于每个消费者,内存中的数据备份是临时性的,如果消费者重新启动,需要从BLOB存储中重新加载快照。

实际上BLOB快照文件的格式很简单,它在很大程度上和在内存布局的结构相同,因此数据初始化主要是将BLOB的内容直接复制到内存中,这一步可以快速完成,确保了初始化的时间很短。

Hollow-Expected class sun.util.calendar.BaseCalendar$Date but object was class java.util.Date

ObjectMapper写入一个数据,包含一个java.util.Date成员,报Expected class sun.util.calendar.BaseCalendar$Date but object was class java.util.Date错误。

这是hollow v2.1.0以下的一个bug,详见:

https://github.com/Netflix/hollow/issues/13

Thanks very much for the report! I verified the issue and released a fix in v2.1.1.

有趣的是,将hollow替换成2.1.1,还是报这个错误,替换成2.2.1就好了

这句I verified the issue and released a fix in v2.1.1.像极了研发的口头禅‘下个版本修复’,承包了我一上午的笑点。

IntelliJ IDEA 生成 serialVersionUID

serialVersionUID的作用

详见这篇文档  http://lenjey.iteye.com/blog/513736

简单来说就是,在序列化对象时,编译器会自动给对象生成一个serialVersionUID,之后任意改动这个对象都会与编译器生成的UID不匹配。如果我们显示指定了serialVersionUID,那么对象序列化之后,也可以修改这个对象。

serialVersionUID生成

我们可以任意指定serialVersionUID,如

但最好还是使用让系统按照摘要算法生成一个指纹数字。eclipse是可以自动生成的,IDEA需要简单配置一下。

IntelliJ IDEA->Preferences->Inspections界面下

Serializable class without serialVersionUID后打钩,注意上面的Profile选为Default。

(继承了Serializable的类没有serialVersionUID就会Warning)

之后光标在继承了Serializable的类名上按 ALT+ENTER键,就会出现 Add serialVersionUID field 选项,生成的UID如下

Jdom-XmlOutputter 换行(setIndent)

Jdom的XmlOutputter默认生成的文件不带换行,所有key-value对写在一行里,使用起来很不方便,XmlOutputter支持设置换行。

jdom1.0中写法如下

XmlOutputter xmlOut = new XmlOutputter("  ", true, "utf-8");

三个参数分别为,缩进(这里是两个空格),是否换行,字符编码

官方文档

jdom1.0以后不支持上面的写法,而是把三个参数剥离出来,形成了Format类

d3总结

项目原因,需要使用web绘制图表,比较靠谱的有echart和d3。

echart:应该是百度的项目,首页做的很漂亮,文档也比较全。个人感觉是一个入手较易,主要在学习怎么配参数,地图是echart的一个亮点。缺点就明显了,入手容易的肯定高度封装过,很难根据自己的需求定制化。而且使用了cavas绘图,在移动端性能不如svg。个人感觉echart适合一些时间比较紧迫,设计没那么明确的场合,直接套上echart,就已经很漂亮了。

d3:大名鼎鼎,受欢迎程度超过jquery,首页的绚丽特效让人眼花,教程的话强烈推荐http://www.ourd3js.com/wordpress/,这个教程好像出了书,叫精通D3.js,基本上把教程撸一遍就没啥大问题了。

d3的优点:用过一段d3后,感觉d3就是svg的jquery,把svg的操作封装为更易用的接口,并提供各种数据可视化的接口。所以d3很灵活,因为你可以用d3在svg中添加一个点、线、饼等等,svg性能不错。链式写法。

缺点:入手稍微慢点,不过撸一遍教程也就一下午的时间。svg的接口网上文档比较少,有些用法stackoverflow都找不到。

如果是学习d3的,推荐前面的教程网站,这里只是个人备忘录,想到哪里说到哪里的流水账,记录那些让人眼前一亮或者眼前一黑的点,以及几个开发中反复查询的用法。

好了,出发

1.

首先,你要在一个dom中添加svg,需要两个参数,width和height,一般我是这么给的

踩坑:需求在指定操作后才绘制该图标,dom display:none的时候,clientWidth和clientHeight是0,所以svg等dom display正常后再添加。

接下来最好设置一下svg的padding,用来应付后面出现的各种文字绘制超出不显示,UI修改等灵细操作。

因为svg直接传的都是px,所以需要根绝屏幕做一下自适应,size_1920函数如下:

坐标轴:

图标中用到最多的是坐标轴,使用坐标轴首先要设定比例尺,目前共用到了4种比例尺

线性比例尺

domain中设置数据的min/max,range中设置svg实际位置。

备注:svg是以左上角为原点的,而常规视角中原点是左下角,所以这里的range起止位置是 svgHeight-0,即起始位置是下边缘y=svgHeight,终止位置是上边缘y=0。这一点需要谨记,后面有许多计算x、y位置的,y的位置总要反着想。其他比例尺也是一样的。

离散比例尺

or

时间比例尺

时间比例尺适用于一些以时间为坐标轴的情况

备注:比如这里是今天到明天,第二天nextDay的算法,就用秒,其他都不行。UTC时间一定要注意,所有时间都改成UTC时间,所有get一律要加UTC。

颜色比例尺

这是个比较特殊的比例尺,不是用来画坐标轴的,而是把颜色均匀分割,category10,category20等等都有,填一个index,输出一个色值。在有些d3的版本中,颜色比例尺没用放在scale里面。

坐标轴

可能还有更多种类的比例尺,目前还没有接触,下面就开始生成坐标轴数据。

几个常用参数

scale,必要参数,把比例尺填进去

orient,非必填,刻度相对坐标轴的位置 top bottom left right

ticks,非必填,刻度个数 注:ticks只是参考,最终生成刻度个数以数据为准,比如数据需要25个刻度,但ticks填了17,那d3就没办法了,会生成一个d3生成的刻度个数中选跟17最接近的。

innerTickSize,非必填,内刻度高,默认是6。 注:这里设置了innerTickSize是整个svg的高度,和y轴innerTickSize设置整个svg的宽配合行程网格效果,正常的坐标轴就再画一个,注意高度计算时错开

outerTickSize,非必填,外刻度高

tickFormat,非必填,刻度值,默认为空  注:这是一个数据绑定参数,即根据数据生成不同的值

tickPadding,非必填,刻度文字与坐标轴的间距

真正的绘制

 

给svg中添加g元素,call绑定坐标轴数据,attr就是给g元素添加各种属性,class(是不是很熟悉),transform。 注:svg中没有top left  margin等属性,大的位移使用transform。

曲线

坐标轴绘制完后,就是曲线的绘制,先生成曲线绘制函数

曲线绘制函数参数

x、y就是根据数据来计算坐标,这里是比例尺大显身手的地方。

interpolate,线段怎么弯曲,讲的最好的是这个地址

http://www.oxxostudio.tw/articles/201411/svg-d3-02-line.html

注:basis曲线保证了优美,没保证曲线与数据的一致,像我这种人是完全无法接受的。

绘制曲线

这一行代码解读一下就是选择所有class是data-line的元素,然后给它绑定数据。data-line哪里来的?这里涉及到d3中一个概念,update  enter  exit

selectAll得到的元素跟数据相比,有三种可能,元素个数 大于/小于/等于数据个数。

数据超出元素个数的部分叫enter,比如第一次绘制时,所有元素都属于paths.enter

paths元素比较简单直接,基本上只需要给一个属性d就能画出来了,d可以用前面的曲线函数直接生成。

stroke,线条颜色,这里可以用颜色比例尺 d3.scale.category10(i)

fill就是填充颜色,paths填充的是一个面。

不超出的部分叫update,即需要更新的部分,paths.update可以看到,也可以直接更新数据,这里是数据驱动的最直观表现

数据少于元素个数的部分叫exit,一般exit只有一个用法

到这里,一个坐标轴差不多出来了,但,是不是少了点啥,对,UED最喜欢的灵魂一击,动画。

曲线进入/更新的时候要动画怎么办

d3的动画很好写

增加 transition ease 和 delay即可,transition前后是动画的起始和结束状态。

绘制一个圆

矩形

半圆角矩形是用path生成的,顶部圆角矩形

topRoundRect和rightRoundRect乍一看完全搞不懂,这里讲的比较详细https://developer.mozilla.org/en-US/docs/Web/SVG/Tutorial/Paths,仔细观察下,就是

M  起始x y坐标

h 纵移

v 横移

a 比较复杂 前两个参数是圆角的x y radius

中间三个参数 001是三个flag,只能是0/1 圆角矩形都是001

第一个代表弧线角度是否要大于180

第二个代表正角度还是负角度

第三个代表正方向还是反方向

z是合拢

最后两个参数是终止的x y位置

当前了,还有更多的L C S等等,这里不研究了

文字

到这里,d3绘制的套路差不多都熟悉了,append data enter transform attr,拿来画个饼图吧!

生成饼图数据

查看一下this.pieData,你会发现数据增加了 startEngle 和 endEngle

注:饼图默认会按大小排序,如果不需要排序,增加一个sort(null)即可

弧线函数

饼图动画不能使用d了,要使用attrTween

简单的饼图动画

高级饼图动画,自己计算delay的时间,让饼一点一点出来

往饼图中间添加一个圆点

注:centriodData和Angle是钝/锐角的计算提出来,是因为在某些低配机子上,偶现绑定计算错误。

画一个折线

到这里,一般的坐标轴、饼图已经难不倒了,而且自己想添加什么就添加什么,无非就是计算x、坐标,d3就是一堆图形函数,帮你计算坐标而已,那还有什么新东西吗?假如UED说想要阴影,光效,这里介绍一下svg里面一个很牛叉的东西,filter

实话说,这个东西我也只是到抄过来调参的地步,比如dx dy。理解上只知道特效输出为result,下一个特效的in是这个result,其他也是一问三不知,而且看了之后着实没有深入了解下去的想法,简直就是手写Photoshop。

下面是几个介绍filter的地址,留下以备不时之需

1.https://www.w3.org/TR/SVG/filters.html

2.https://www.smashingmagazine.com/2015/05/why-the-svg-filter-is-awesome/

3.https://jorgeatgu.github.io/svg-filters/