In the project, when a statistical demand is encountered, statistics are made from a table according to conditions, and count(distinct case when) is used to solve this problem.

count() function

In statistics, count appears most frequently

Simplest usage

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

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

count(1), count(*) and count (field). What are the differences between them?

View all table index SQL statements

SHOW INDEX FROM table_name;

• count(1) and count(*)

There is little difference between count(1) and count(*). Through explain, it is found that MySQL will help you automatically optimize (use the primary key index when there is only a primary key index, use the unique index when there is a unique index and a primary key, select the common index when there is a common index and a primary key index, and select the index with the least space when there are multiple secondary indexes), Specify to the fastest field or index tree (composite index).

The table has primary keys and unique indexes

 

Table structure information sql statement (SHOW CREATE TABLE `t_product_events_params')

CREATE TABLE `t_product_events_params` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Product type event parameters ID',
  `ref_id` bigint(20) NOT NULL COMMENT 'event ID',
  `identifier` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(64) NOT NULL COMMENT 'Parameter name',
  `description` varchar(256) DEFAULT NULL COMMENT 'Parameter description',
  `data_type` int(11) NOT NULL COMMENT 'Parameter data type',
  `specs` longtext NOT NULL COMMENT 'Data specification',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ref` int(11) NOT NULL,
  `custom` tinyint(2) DEFAULT NULL COMMENT 'Template import, 0:',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `identifier_ref_id` (`ref_id`,`identifier`) USING BTREE COMMENT 'Under the same function definition, identifier Duplicate is not allowed'
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='Product type event output data specification table'

Table with normal index and primary key index

 

 

 

Table with primary key index only

The indexes in the table are shown in the figure below

 

 

According to explain, count(1) and count(*) are not much different!!!

Principle:

For the operation of counting the number of rows, the optimization direction of the query optimizer is to select the index with the least IO times, that is, the index built based on the field with the smallest space (the amount of data read by each IO is fixed, and the smaller the space occupied by the index, the less IO times required). The primary KEY index of Innodb is a clustered index (including the primary KEY, the whole record information corresponding to the primary KEY, transaction ID, MVCC and rollback pointer), so the primary KEY index must be larger than the secondary index (including KEY and corresponding primary KEY ID), that is, when there is a secondary index, COUNT() generally does not count the number of rows through the primary KEY index, When there are multiple secondary indexes, select the one with the smallest space!!!

• count(*) and count (field)

count(*) will count all rows, and count (field) will not count null values

count(case when)

Conditional statistics, that is, conditional statistics for a table, such as table test_db, with a field user_ ID (possibly repeated), gender(man, women), and the number of men and women needs to be counted

You can use where to make statistics separately

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'

You can also use conditional statistics

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

More SQL complex querieshttps://blog.csdn.net/Be_insighted/article/details/107683361?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522164217052716780271580287%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fblog.%2522%257D&request_id=164217052716780271580287&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~first_rank_ecpm_v1~rank_v31_ecpm-27-107683361.nonecase&utm_term=%E7%B4%A2%E5%BC%95&spm=1018.2226.3001.4450