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

 

Raspberry Pi Zero W解决调试信息不足的问题(以libzmq-dev为例)

最近在参照 树莓派实时系统下脚本语言的选择(应当使用Lua而不是Python) 调用调试lua-zmq的时候,发现使用PAIR模式进行线程之间通信,长时间运行后会出现死锁的情况。

当我们需要跟踪问题的时候,使用apt-get安装的版本缺乏必要的调试信息。

我们可以手工安装调试信息包,如下:


我们也可以从源代码重新编译一份,如下:

以上是针对Debian安装包来进行的处理,然而对于luarocks安装的插件来说,默认luarocks是不能编译调试版本的,此时就需要我们手工编译了。我们以lua-zmq为例子,参考如下:

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最大列 解决。

解决Raspberry PI Zero W中Lua使用lua-periphery与Python中设置的GPIO端口不一致的问题

树莓派实时系统下脚本语言的选择(应当使用Lua而不是Python)中,我们没有使用rpi-gpio,而是使用了lua-periphery来解决Lua语言下操作树莓派GPIO的问题。

当时选择lua-periphery的原因在于rpi-gpioRaspberry PI Zero W中使用的时候会崩溃。这个原因是在于cpuinfo.c这个文件中缺少对于BCM2835这颗新的CPU的判断,只判断了BCM2708估计写这个库的时候,只有BCM2708)。导致RPi_GPIO_Lua_module.c在初始化GPIO的时候抛出了异常。这个已经有人提交了代码合并请求,估计很快会修复。

但是在lua-periphery中,没有对于GPIO进行重新映射,导致跟rpi-gpio以及树莓派自带的Python库在设置GPIO的时候,端口号对应不一致。比如,在Python中设置GPIO 22,执行命令观察ls /sys/class/gpio/,会发现系统创建的是GPIO 25这个对应关系就是通过查表获取的。如下图:
继续阅读解决Raspberry PI Zero W中Lua使用lua-periphery与Python中设置的GPIO端口不一致的问题

解决Raspberry Pi安装libgtk2.0-dev出错的问题

最近在树莓派上需要安装libgtk2.0-dev,执行如下命令:

出错信息如下:

根据出错信息,明显是软件源中出现了安装包缺失的问题。这个现象是不应该出现的。网上查询了不少地方,最终找到解决方法:

可以看到如下内容:

默认里面的内容都是被注释掉的,我们需要做的就是把这个源打开即可。
也可以直接执行如下命令来开启:

参考链接


Unable to install libgtk2.0-dev on Raspberry Pi 2

修正ubuntu 18.04上执行"sudo apt upgrade"报告"libc6-dev-armhf-cross"升级出错

最近自己电脑上的ubuntu 18.04在更新软件的时候报告如下错误信息:

如果系统语言为英文,则错误信息如下:

解决方法为卸载后重新安装,而不使用升级安装:

参考链接


How do I fix an error with libc6-dev-armhf-cross in Ubuntu 18.04 when trying to apt upgrade?

Lua的集成开发环境ZeroBrane Studio

目前在使用Lua进行脚本的开发,可是官方并没有提供很好的集成开发环境。
体验了很多,发现ZeroBrane Studio这个开源软件还是非常好用的,并且已经能正常支持LinuxWindowsMacOS这三个主流平台。

 

建议去官方网站下载最新的版本,但是鉴于国内网络访问不是非常稳定,可以从本站下载一份目前最新的版本。

下面的版本根据自身操作系统来选择其中一个进行下载

ZeroBraneStudioEduPack-1.70-linux.sh

ZeroBraneStudioEduPack-1.70-macos.dmg

ZeroBraneStudioEduPack-1.70-win32.exe

树莓派下的编译(目前编译出的暂时无法使用):

参考链接


Debugging Lua Code
Lightweight IDE for your Lua needs ZeroBrane Studio

解决Raspberry Pi Zero W启动后没有在HDMI口输出内容的问题

最新在使用的Raspberry Pi Zero W V1.3在使用目前(2018.09.26)的系统的时候发现无法正常输出内容到屏幕上面,屏幕一直黑屏无信号。

原因在于Raspberry Pi Zero W在启动的时候没有正确检测到屏幕信号,导致没有正常输出。

解决方法是打开启动配置文件/boot/config.txt, 找到如下内容:


然后去掉注释,修改为如下: