# 人分九等，数有阶梯 - PostgreSQL 阶品（颗粒）分析函数width_bucket, kmean应用

+关注继续查看

### 标签

PostgreSQL , width_bucket , 数据分布 , 包裹筛选 , 颗粒度筛选 , Oracle , 阶级分布 , kmean

## 背景

1、对全国高考成绩进行分布透视，你的高考成绩拿出来，会落在第几等呢？

2、气温分布，你所在的城市，全年的平均气温会在全国排第几等呢？

3、雨量分布，你所在的城市，全年的降雨量会在全国排在第几等呢？

4、包裹分拣，按重量、按体积进行分拣，方便物流的运输。

5、商品颗粒筛选，例如大米、枸杞、罗汉果、水果、大闸蟹等商品，按颗粒度的大小，分为不同的等级。

6、收入等级，你的薪资水平落在第几等呢？是不是戳中小心脏了，是不是又拖全国平均工资后腿了呢？

7、用水用电等级，现在水电都是阶梯价，不同的阶梯，定价都不一样。

8、交税也按收入分等级。

9、按每年接待的游客数分几个档，评选出不同级别的景区。

10、对玩王者荣耀的时间进行统计，按游戏时长，对人群进行归类。

11、对淘宝店铺的点击率、销量数据进行统计，划分店铺等级。

#### 1、width_bucket

1、指定预设边界和等级个数，返回VALUE所处等级。

2、指定预设边界数组，返回VALUE所处等级。

#### 2、kmean

PostgreSQL kmeans插件：

《K-Means 数据聚集算法》

## 一、分类利器1 width_bucket

### 例子1

1、设计表结构

create table test(
sid int,   -- 店铺ID
cnt_date date,  -- 日期
cnt int,  -- 浏览量
primary key (sid,cnt_date)  -- 主键约束
);


2、生成正态分布的销量数据

vi test.sql

\set cnt random_gaussian(0,10000,4)
\set sid random(1,10000000)
insert into test values (:sid, '2017-07-15', :cnt) on conflict (sid,cnt_date) do nothing;


pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120



3、等级统计透视

postgres=# select sid, cnt_date, cnt, width_bucket(cnt, 100, 5000, 10) as wb from test limit 10;
sid   |  cnt_date  | cnt  | wb
---------+------------+------+----
1799658 | 2017-07-15 | 5708 | 11
9549703 | 2017-07-15 | 5016 | 11
2122532 | 2017-07-15 | 4413 |  9
7663952 | 2017-07-15 | 6199 | 11
7047657 | 2017-07-15 | 5655 | 11
8485951 | 2017-07-15 | 6902 | 11
5135164 | 2017-07-15 | 5929 | 11
5592226 | 2017-07-15 | 4213 |  9
3389938 | 2017-07-15 | 4091 |  9
1372024 | 2017-07-15 | 5505 | 11
(10 rows)

postgres=# select width_bucket(cnt, 100, 5000, 10) as wb, count(*) from test where cnt_date='2017-07-15' group by 1 order by 1;
wb |  count
----+---------
0 |     129
1 |    1635
2 |    6368
3 |   21686
4 |   62661
5 |  155530
6 |  332831
7 |  610253
8 |  961658
9 | 1303200
10 | 1517335
11 | 4975181
(12 rows)


4、等级占比透视

select wb, cnt, round(100*(cnt/(sum(cnt) over ())), 2)||' %' as ratio from
(select width_bucket(cnt, 100, 5000, 10) as wb, count(*) cnt from test where cnt_date='2017-07-15' group by 1) t order by wb;

wb |   cnt   |  ratio
----+---------+---------
0 |     129 | 0.00 %
1 |    1635 | 0.02 %
2 |    6368 | 0.06 %
3 |   21686 | 0.22 %
4 |   62661 | 0.63 %
5 |  155530 | 1.56 %
6 |  332831 | 3.35 %
7 |  610253 | 6.13 %
8 |  961658 | 9.67 %
9 | 1303200 | 13.10 %
10 | 1517335 | 15.25 %
11 | 4975181 | 50.01 %
(12 rows)


### 例子2

<100

[100, 500)

[500,1000)

[1000,2000)

[2000,5000)

[5000,8000)

>=8000

postgres=# select width_bucket(cnt, array[100,500,1000,2000,5000,8000]) as wb, count(*) as cnt from test where cnt_date='2017-07-15' group by 1 order by 1;
wb |   cnt
----+---------
0 |     129
1 |    1152
2 |    5245
3 |   74644
4 | 4892116
5 | 4893590
6 |   81591
(7 rows)

select wb, cnt, round(100*(cnt/(sum(cnt) over ())), 2)||' %' as ratio from
(select width_bucket(cnt, array[100,500,1000,2000,5000,8000]) as wb, count(*) as cnt from test where cnt_date='2017-07-15' group by 1) t order by wb;

wb |   cnt   |  ratio
----+---------+---------
0 |     129 | 0.00 %
1 |    1152 | 0.01 %
2 |    5245 | 0.05 %
3 |   74644 | 0.75 %
4 | 4892116 | 49.17 %
5 | 4893590 | 49.19 %
6 |   81591 | 0.82 %
(7 rows)


## 二、分类利器2 kmeans

### 例子1

postgres=# select sid,cnt_date,cnt,kmeans(array[cnt], 10) over () k from test where cnt_date='2017-07-15' limit 10;
sid   |  cnt_date  | cnt  | k
---------+------------+------+---
1799658 | 2017-07-15 | 5708 | 6
9549703 | 2017-07-15 | 5016 | 5
2122532 | 2017-07-15 | 4413 | 3
7663952 | 2017-07-15 | 6199 | 7
7047657 | 2017-07-15 | 5655 | 6
8485951 | 2017-07-15 | 6902 | 8
5135164 | 2017-07-15 | 5929 | 6
5592226 | 2017-07-15 | 4213 | 3
3389938 | 2017-07-15 | 4091 | 3
1372024 | 2017-07-15 | 5505 | 5
(10 rows)

postgres=# select k, count(*) as cnt from (select sid,cnt_date,cnt,kmeans(array[cnt], 10) over () k from test where cnt_date='2017-07-15') t group by 1 order by 1;
k |   cnt
---+---------
0 |  244257
1 |  674861
2 | 1084717
3 | 1399515
4 | 1569936
5 | 1568358
6 | 1399082
7 | 1087206
8 |  675811
9 |  244724
(10 rows)


postgres=# select sid,cnt_date,cnt,kmeans(array[cnt], 6, array[100,500,1000,2000,5000,8000]) over () k from test where cnt_date='2017-07-15' limit 10;
sid   |  cnt_date  | cnt  | k
---------+------------+------+---
1799658 | 2017-07-15 | 5708 | 3
9549703 | 2017-07-15 | 5016 | 3
2122532 | 2017-07-15 | 4413 | 2
7663952 | 2017-07-15 | 6199 | 4
7047657 | 2017-07-15 | 5655 | 3
8485951 | 2017-07-15 | 6902 | 5
5135164 | 2017-07-15 | 5929 | 4
5592226 | 2017-07-15 | 4213 | 2
3389938 | 2017-07-15 | 4091 | 1
1372024 | 2017-07-15 | 5505 | 3
(10 rows)

postgres=# select k, count(*) as cnt from (select sid,cnt_date,cnt,kmeans(array[cnt], 6, array[100,500,1000,2000,5000,8000]) over () k from test where cnt_date='2017-07-15') t group by 1 order by 1;
k |   cnt
---+---------
0 |  731010
1 | 1788146
2 | 2428678
3 | 2438930
4 | 1813311
5 |  748392
(6 rows)


select k, cnt, round(100*(cnt/(sum(cnt) over ())), 2)||' %' as ratio from
(select k, count(*) as cnt from (select sid,cnt_date,cnt,kmeans(array[cnt], 10) over () k from test where cnt_date='2017-07-15') t group by 1) t order by k;
k |   cnt   |  ratio
---+---------+---------
0 |  244257 | 2.46 %
1 |  674861 | 6.78 %
2 | 1084717 | 10.90 %
3 | 1399515 | 14.07 %
4 | 1569936 | 15.78 %
5 | 1568358 | 15.76 %
6 | 1399082 | 14.06 %
7 | 1087206 | 10.93 %
8 |  675811 | 6.79 %
9 |  244724 | 2.46 %
(10 rows)

select k, cnt, round(100*(cnt/(sum(cnt) over ())), 2)||' %' as ratio from
(select k, count(*) as cnt from (select sid,cnt_date,cnt,kmeans(array[cnt], 6, array[0,100,500,2000,5000,10000]) over () k from test where cnt_date='2017-07-15') t group by 1) t order by k;
k |   cnt   |  ratio
---+---------+---------
0 |  731010 | 7.35 %
1 | 1788146 | 17.97 %
2 | 2428678 | 24.41 %
3 | 2438930 | 24.52 %
4 | 1813311 | 18.23 %
5 |  748392 | 7.52 %
(6 rows)


### Classification

When the desired output is categorical in nature, we use classification methods to build a model that predicts which of the various categories a new result would fall into. The goal of classification is to be able to correctly label incoming records with the correct class for the record.

Example: If we had data that described various demographic data and other features of individuals applying for loans, and we had historical data that included what past loans had defaulted, then we could build a model that described the likelihood that a new set of demographic data would result in a loan default. In this case, the categories are “will default” or “won’t default” which are two discrete classes of output.

### Regression

When the desired output is continuous in nature, we use regression methods to build a model that predicts the output value.

Example: If we had data that described properties of real estate listings, then we could build a model to predict the sale value for homes based on the known characteristics of the houses. This is a regression problem because the output response is continuous in nature, rather than categorical.

### Clustering

Here we are trying to identify groups of data such that the items within one cluster are more similar to each other than they are to the items in any other cluster.

Example: In customer segmentation analysis, the goal is to identify specific groups of customers that behave in a similar fashion, so that various marketing campaigns can be designed to reach these markets. When the customer segments are known in advance this would be a supervised classification task. When we let the data itself identify the segments, this becomes a clustering task.

### Topic Modeling

Topic modeling is similar to clustering in that it attempts to identify clusters of documents that are similar to each other, but it is more specific to the text domain where it is also trying to identify the main themes of those documents.

### Association Rule Mining

Also called market basket analysis or frequent itemset mining, this is attempting to identify which items tend to occur together more frequently than random chance would indicate, suggesting an underlying relationship between the items.

Example: In an online web store, association rule mining can be used to identify what products tend to be purchased together. This can then be used as input into a product recommendation engine to suggest items that may be of interest to the customer and provide upsell opportunities.

### Descriptive Statistics

Descriptive statistics don’t provide a model and thus are not considered a learning method. However, they can be helpful in providing information to an analyst to understand the underlying data, and can provide valuable insights into the data that may influence choice of data model.

Example: Calculating the distribution of data within each variable of a dataset can help an analyst understand which variables should be treated as categorical variables, and which should be treated as continuous variables, including the sort of distribution the values fall in.

### Validation

Using a model without understanding the accuracy of that model can lead to a poor outcome. For that reason, it is important to understand the error of a model and to evaluate the model for accuracy on test data. Frequently in data analysis, a separation is made between training data and test data solely for the purpose of providing statistically valid analysis of the validity of the model, and assessment that the model is not over-fitting the training data. N-fold cross validation is also frequently utilized.

## 四、为什么PostgreSQL比Oracle先进

1、Oracle width_bucket不支持数组，只支持均匀分布透视，不支持非均匀分布的数据透视。

2、PostgreSQL支持众多机器学习算法。

3、PostgreSQL 支持生成正态分布，随机分布，泊松分布的测试数据，便于测试。

https://www.postgresql.org/docs/10/static/pgbench.html

## 更多Mathematical函数

https://www.postgresql.org/docs/10/static/functions-math.html

## 参考

https://www.postgresql.org/docs/10/static/functions-math.html

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions214.htm

https://www.postgresql.org/docs/9.6/static/pgbench.html

《在PostgreSQL中如何生成测试kmean算法的数据》

《K-Means 数据聚集算法》

《生成泊松、高斯、指数、随机分布数据 - PostgreSQL 9.5 new feature - pg_bench improve, gaussian (standard normal) & exponential distribution》

《PostgreSQL 9.5 new feature - width_bucket return the bucket number》

《PostgreSQL FDW mongo_fdw usage》

Kubernetes Ingress 日志分析与监控的最佳实践
Ingress 主要提供 HTTP 层（7 层）路由功能，是目前 K8s 中 HTTP/HTTPS 服务的主流暴露方式。为简化广大用户对于 Ingress 日志分析与监控的门槛，阿里云容器服务和日志服务将 Ingress 日志打通，只需要应用一个 yaml 资源即可完成日志采集、分析、可视化等一整套 Ingress 日志方案的部署。
5000 0

4286 0

20052 0
Java Swing 是否适合做大型项目及桌面应用？

1022 0

160 0
《区块链DAPP开发入门、代码实现、场景应用》笔记4——Ethereum Wallet中部署合约

1510 0
+关注

2153

245

+ 订阅

《2021云上架构与运维峰会演讲合集》

《零基础CSS入门教程》

《零基础HTML入门教程》