所謂的數據稠化，就是補全缺失的數據。因為在數據庫表中，存儲的數據經常是稀疏的(sparse data),也就是不完整的。比如記錄一個員工每個月的銷售額，用這么一個銷售表來記錄：SalesRecord(Name(姓名),Date(日期),Sales(銷售額))，假設某個月這個員工請假沒上班，對應的沒有銷售額，一般也不會將這個員工的銷售額存儲為0，而是直接不存儲，這樣在銷售表中就會產生缺失的行，導致的結果就是這些銷售數據在時間上是不連續的，或者說就是缺失的。為了后續的一些統計，需要對數據進行補全也就是所謂的數據稠化。

下面做一個簡單的例子，

圖 1

需要知道每個人每科的成績，這里就是需要補全數據，比如(Lucy的Chinese成績？？)，因此，最終我們需要的到

的結果是這樣的(紅色背景是補全的內容)：

圖 2

步驟如下：

先創建一張成績表Scores

1 --創建Scores表

2 create tableScores(3 stuName varchar2(10),4 subject varchar2(10),5 score number );

然后插入數據，得到圖1.

接下來，我們是實現一維(學科)數據稠密，也就是對學科，每個人都有每個學科，首先想到的是要找出所有學科(暫且這么做，以后經常是對另外一張學科表join)，

廢話少說，找出所有學科：

select distinct subject from Scores;

我們可以根據原表利用partition by()語法來進行下一步操作

1 --一維稠密數據

2 SELECTscores.stuname,3 m.subject,4 NVL(scores.score,0)5 FROMscores6 PARTITION BY(scores.stuname) --這里是重點7 right join

8 (SELECT DISTINCT subject FROMscores) m9 ON scores.subject=m.subject;

最終得到結果：

顯然，上面的代碼看起來很雜亂，我們可以來個with as 語法，使代碼看起來清晰：

withv1as (select distinct subject fromscores),SELECT scores.stuname,v1.subject,NVL(scores.score,0) FROMscores

PARTITIONBY(scores.stuname)right joinv1ON scores.subject=v1.subject;

實現了一維的數據稠密，那么給表再添加個字段—年份，需要知道每個人，每年的每科成績又怎么稠密呢?同樣的道理

先實現一維的稠化，再在稠化后的基礎上再稠化一次，以此類推就可以了嘛！

withv1as (select * fromscores), --這是原表

v2as (select distinct subject fromscores), --所有科目

v3as (select distinct dateyear fromscores), --所有年份

v4as (select v1.stuname,v2.subject,v1.score,v1.dateyear fromv1

partitionby(v1.stuname)right joinv2on v1.subject =v2.subject) --v4就是對學科稠密化后的表，如圖2所示select v4.stuname , v4.subject , NVL(v4.score,0),v3.dateyear fromv4

partitionby(stuname,subject) --注意這里right joinv3 --最后在對v4進行年份的稠密,on v4.dateyear=v3.dateyear;

這樣就大功告成啦，每個人，每年的每課成績均可有了。接下來，需要對著表進行行列轉換如下圖所示，這里我們以后再說！

下面實現二維數據稠化，我們同樣有如下表：

YEARMONTH

STUDENT

SUBJECT

SCORE

201601

Jim

Chinese

78

201601

Jim

Math

34

201603

Jim

English

89

201605

Jim

Physics

88

201608

Jim

Math

67

201601

Joe

Math

87

201602

Joe

Chinese

87

201604

Joe

Chinese

55

201609

Joe

Math

45

201609

Joe

Physics

90

YEARMONTH

STUDENT

SUBJECT

SCORE

201601

Jim

Chinese

78

201601

Jim

Math

34

201601

Jim

English

0

201601

Jim

Physics

0

201602

Jim

Chinese

0

201602

Jim

Math

0

201602

Jim

English

0

201602

Jim

Physic

0

201603

Jim

Chinese

0

201603

Jim

Math

0

201603

Jim

English

89

201603

Jim

Physics

0

如右表所示，部分補全數據為紅色背景的數據，其分數默認為0，這樣我們就能看到

每個人(student維度)在所有時間(yearmonth維度)的每科(subject維度)的分數(score度量)

我們的做法應該是怎么樣呢？

我們先做前期的準備，創建一張成績表，并插入相應的數據

--創建學生成績表

CREATE TABLEstu_score (

yearmonthnumber,

studentVARCHAR2(20),

subjectvarchar2(20),

scorenumber)--往表中插入數據

INSERT INTO stu_score VALUES(201601,'Jim','Chinese',78);INSERT INTO stu_score VALUES(201601,'Jim','Math',34);INSERT INTO stu_score VALUES(201603,'Jim','English',89);INSERT INTO stu_score VALUES(201605,'Jim','Physics',88);INSERT INTO stu_score VALUES(201608,'Jim','Math',67);INSERT INTO stu_score VALUES(201601,'Joe','Math',87);INSERT INTO stu_score VALUES(201602,'Joe','Chinese',87);INSERT INTO stu_score VALUES(201604,'Joe','Chinese',55);INSERT INTO stu_score VALUES(201609,'Joe','Math',45);INSERT INTO stu_score VALUES(201609,'Joe','Physics',90);

同樣，創建一張時間維度表

--創建時間維度表

CREATE TABLEDIM_DATE (

yearmonthnumber);INSERT INTO DIM_DATE VALUES(201601);INSERT INTO DIM_DATE VALUES(201602);INSERT INTO DIM_DATE VALUES(201603);INSERT INTO DIM_DATE VALUES(201604);INSERT INTO DIM_DATE VALUES(201605);INSERT INTO DIM_DATE VALUES(201606);INSERT INTO DIM_DATE VALUES(201607);INSERT INTO DIM_DATE VALUES(201608);INSERT INTO DIM_DATE VALUES(201609);INSERT INTO DIM_DATE VALUES(201610);INSERT INTO DIM_DATE VALUES(201611);INSERT INTO DIM_DATE VALUES(201612);

然后，我們需要做的就是去稠化這些數據，保證在每個維度都有數據

WITH sub AS(SELECT DISTINCT subject FROMstu_score

),

t1as(SELECT t.yearmonth,t.student,sub.subject,t.score FROMstu_score t

PARTITIONBY (t.student) RIGHT JOINsubON t.subject=sub.subject)SELECT dim_date.yearmonth,t1.student,t1.subject,nvl(t1.score,0) FROMt1

PARTITIONBY(student,subject)right JOIN DIM_DATE ON dim_date.yearmonth = t1.yearmonth;

或者，不用創建臨時表，直接合并

WITH sub AS ( --學科維度表，將所有學科選出

SELECT DISTINCT subject FROMstu_score

),SELECT dim_date.yearmonth,t1.student,t1.subject,nvl(t1.score,0) FROM(SELECT t.yearmonth,t.student,sub.subject,t.score FROMstu_score t

PARTITIONBY (t.student) RIGHT JOINsubON t.subject=sub.subject)t1 --對學科稠化，每個人在每個學科都有數據

PARTITION BY(student,subject)right JOIN DIM_DATE ON dim_date.yearmonth = t1.yearmonth; --對日期稠化，保證每個日期都有數據

總結

以上是生活随笔為你收集整理的mysql稠化报表_Oracle使用PARTITION BY 实现数据稠化报表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。