BI&BigData
  • Register
תנו לנו לייק וקבלו עדכונים ישירות לפיד

דירוג משתמשים: 0 / 5

Star inactiveStar inactiveStar inactiveStar inactiveStar inactive
 

כשאנו מאחזרים מידע , הרבה פעמים אנו צריכים לשטח טבלה לאחזור נכון יותר. להלן טריק יפה באורקל.

בואו נראה את הדוגמא הבאה:
יש לנו טבלה מכירות המכילה את פרטי המכירה:

sales

 

טבלת לקוחות המפענחת את cust_id:

customers

וכן טבלת סיווגים, המתארת את סיווג הלקוח:

classifications

 

אם נחבר את טבלת SALES וטבלת CUSTOMERS , הכל יהיה תקין, כלומר הSQL הבא:

select b.cust_id, b.cust_name,a.sales
from sales a
inner join customers b
on a.cust_id=b.cust_id

 

הבעיה מתחילה כשרוצים להוסיף גם את טבלת הclassification. במקרה כזה, בגלל שלכל לקוח יכול להיות יותר מסיווג אחד, נקבל הכפלת רשומות, כלומר:

אם נריץ:

select b.cust_id, b.cust_name,sum(a.sales) as sum
from sales a
inner join customers b on a.cust_id=b.cust_id
inner join classifications c on b.cust_id=c.cust_id
group by b.cust_id, b.cust_name

נקבל:

results1

 שימו לב: עצם העובדה שחיברנו את טבלת classification הכפילה את sales כמספר הסיווגים הקיימים בטבלת classification ללקוח!

מאחר ולכל לקוח יכול להיות מספר סיווגים נמוך יחסית (3 במקרה זה), נוכל לשטח את נתוני הסיווג. לצורך זה קיימת באורקל פונקציה מיוחדת בשם listagg.

תחבירה הוא:

listagg(column,delimiter) within group (order by column)

האגרגומנטים:

column-השדה אותו רוצים לשרשר (במקרה שלנו classification)

delimiter- המפריד (נבחר ;)

order by column- סדר הערכים בשרשור (נכתוב שרירותית את הclassification עצמו, כלומר מיון אלפבתי)

פונקציה זאת היא פונקציה אגרגטיבית, כלומר, במידה ומשתמשים בשדה נוסף, יש לכתוב את משפט הGroup By.

כלומר המשפט המלא יהיה:

select
cust_id,listagg (classification,';') within group (order by classification) as flat_classification
from
classifications
group by
cust_id

אשר יחזיר את התוצאה הבאה.

 results2

מכן, נוכל להשתמש בDerived Table, כדי להציג את שם הלקוח, המכירות שלו וכן את הסיווגים שלו. למשל:

select b.cust_id, b.cust_name,c.flat_classification, sum(a.sales) as sum
from sales a
inner join customers b on a.cust_id=b.cust_id
inner join
(select
cust_id,listagg (classification,';') within group (order by classification) as flat_classification
from
classifications
group by
cust_id) c on b.cust_id=c.cust_id
group by b.cust_id, b.cust_name,c.flat_classification

שיחזיר:

results3

 

וסיימנו.

אם אתם רוצים לנסות בבית, להלן קובץ הקמת סביבה:

CREATE TABLE "CLASSIFICATIONS" 
   (	"CUST_ID" NUMBER, 
	"CLASSIFICATION" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Table CUSTOMERS
--------------------------------------------------------

  CREATE TABLE "CUSTOMERS" 
   (	"CUST_ID" NUMBER, 
	"CUST_NAME" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  DDL for Table SALES
--------------------------------------------------------

  CREATE TABLE "SALES" 
   (	"CUST_ID" NUMBER, 
	"SALES" NUMBER
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
REM INSERTING into CLASSIFICATIONS
SET DEFINE OFF;
Insert into CLASSIFICATIONS (CUST_ID,CLASSIFICATION) values (1,'Business');
Insert into CLASSIFICATIONS (CUST_ID,CLASSIFICATION) values (1,'VIP');
Insert into CLASSIFICATIONS (CUST_ID,CLASSIFICATION) values (1,'Personal');
Insert into CLASSIFICATIONS (CUST_ID,CLASSIFICATION) values (2,'VIP');
Insert into CLASSIFICATIONS (CUST_ID,CLASSIFICATION) values (2,'Business');
Insert into CLASSIFICATIONS (CUST_ID,CLASSIFICATION) values (3,'VIP');
Insert into CLASSIFICATIONS (CUST_ID,CLASSIFICATION) values (3,'Personal');
REM INSERTING into CUSTOMERS
SET DEFINE OFF;
Insert into CUSTOMERS (CUST_ID,CUST_NAME) values (1,'John Smith');
Insert into CUSTOMERS (CUST_ID,CUST_NAME) values (2,'Don Gordon');
Insert into CUSTOMERS (CUST_ID,CUST_NAME) values (3,'Lynn Levy');
REM INSERTING into SALES
SET DEFINE OFF;
Insert into SALES (CUST_ID,SALES) values (1,1000);
Insert into SALES (CUST_ID,SALES) values (2,3000);
Insert into SALES (CUST_ID,SALES) values (3,1500);
commit;

Sap

Oracle

Informatica

SAS

Microstrategy

IBM

Microsoft

Qlikview

Teradata

Webfocus

התחברות

x
יצירת חשבון
x

קליק אחד ואתם מחוברים. מהיר .קל .מאובטח.

מעניין? שתפו דף זה באמצעות הטלפון הנייד

שיטוח נתונים באורקל באמצעות LISTAGG - QR Code Friendly
Powered by QR Code Friendly

מאמרים

מגמות של ביג דאטה בעולם הביטוח
CA Technologies
SSIS - Buffer Size Optimization
קטגוריה ראשית
בדיקות BI ו-DWH לעומת הבדיקות בתחומים אחרים
קטגוריה ראשית
איסוף דרישות לפרויקטי BI
קטגוריה ראשית
כח המידע במיקוד
קטגוריה ראשית
0

הדף שלנו בפייסבוק

התחברות

חדש בישראל

כניסות למאמרים
7378612