Khám phá phân phối

Phân tích Khám phá Dữ liệu bằng SQL

Christina Maimone

Data Scientist

Đếm giá trị

SELECT unanswered_count, count(*)
  FROM stackoverflow
 WHERE tag='amazon-ebs'
 GROUP BY unanswered_count
 ORDER BY unanswered_count;

 unanswered_count | count 
------------------+-------
               37 |    12
               38 |    40
...
               43 |    10
               44 |     8
               45 |    17
               46 |     4
               47 |     1
...
               54 |   131
               55 |    34
               56 |     1
(20 hàng)

Truncate

SELECT trunc(42.1256, 2);

42.12

SELECT trunc(12345, -3);

Cắt bớt và nhóm

SELECT trunc(unanswered_count, -1) AS trunc_ua, 
       count(*)
  FROM stackoverflow
 WHERE tag='amazon-ebs'
 GROUP BY trunc_ua   -- bí danh cột
 ORDER BY trunc_ua;  -- bí danh cột

 trunc_ua | count 
----------+-------
       30 |    74
       40 |   194
       50 |   480
(3 hàng)

Tạo dãy

SELECT generate_series(start, end, step);

Tạo dãy

SELECT generate_series(1, 10, 2);

 generate_series 
-----------------
               1
               3
               5
               7
               9
(5 hàng)

SELECT generate_series(0, 1, .1);

 generate_series 
-----------------
               0
             0.1
             0.2
             0.3
             0.4
             0.5
             0.6
             0.7
             0.8
             0.9
             1.0
(11 hàng)

Tạo bin: kết quả

 lower | upper | count 
-------+-------+-------
    30 |    35 |     0
    35 |    40 |    74
    40 |    45 |   155
    45 |    50 |    39
    50 |    55 |   445
    55 |    60 |    35
    60 |    65 |     0
(7 hàng)

Tạo bin: truy vấn

-- Tạo bin
WITH bins AS (
      SELECT generate_series(30,60,5) AS lower,
             generate_series(35,65,5) AS upper), 














               ;

Tạo bin: truy vấn

-- Tạo bin
WITH bins AS (
      SELECT generate_series(30,60,5) AS lower,
             generate_series(35,65,5) AS upper), 
     -- Lọc dữ liệu theo tag quan tâm
     ebs AS (
      SELECT unanswered_count
        FROM stackoverflow
       WHERE tag='amazon-ebs')









               ;

Tạo bin: truy vấn

-- Tạo bin
WITH bins AS (
      SELECT generate_series(30,60,5) AS lower,
             generate_series(35,65,5) AS upper), 
     -- Lọc dữ liệu theo tag quan tâm
     ebs AS (
      SELECT unanswered_count
        FROM stackoverflow
       WHERE tag='amazon-ebs')
-- Đếm giá trị trong mỗi bin
SELECT lower, upper, count(unanswered_count) 
  -- left join giữ mọi bin
  FROM bins
       LEFT JOIN ebs
              ON unanswered_count >= lower
             AND unanswered_count < upper


               ;

Tạo bin: truy vấn

-- Tạo bin
WITH bins AS (
      SELECT generate_series(30,60,5) AS lower,
             generate_series(35,65,5) AS upper), 
     -- Lọc dữ liệu theo tag quan tâm
     ebs AS (
      SELECT unanswered_count
        FROM stackoverflow
       WHERE tag='amazon-ebs')
-- Đếm giá trị trong mỗi bin
SELECT lower, upper, count(unanswered_count) 
  -- left join giữ mọi bin
  FROM bins
       LEFT JOIN ebs
              ON unanswered_count >= lower
             AND unanswered_count < upper
 -- Group by ranh giới bin để tạo nhóm
 GROUP BY lower, upper
 ORDER BY lower;

Tạo bin: kết quả

 lower | upper | count 
-------+-------+-------
    30 |    35 |     0
    35 |    40 |    74
    40 |    45 |   155
    45 |    50 |    39
    50 |    55 |   445
    55 |    60 |    35
    60 |    65 |     0
(7 hàng)

Đến lúc khám phá các phân phối!

Phân tích Khám phá Dữ liệu bằng SQL