Sample Code for implementing the foreign key function of the cube

If field or field combination K is the primary key of table m and K also exists in Table B, k is the foreign key of Table B. Foreign keys maintain the association between tables and are one of the most important concepts in structured data computing. The encoder can easily implement the foreign key function through object reference. The following describes several examples:

Example 1 associate a primary table with a subtable1:

The Order table (Order) is a sub-table, and the employee table (EMP) is a master table. Associate EMP with order to display the name, gender, salary field, and orderid in order in EMP, amount field.

Note: In this example, only EMP and order are used, and the department table (DEP) is used in the subsequent example. The foreign key relationship among the three is as follows.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/45/B2/wKioL1PpsNLwPCb_AAE1bHz5SI0199.jpg "style =" float: none; "Title =" esproc_foreign_key_1.jpg "alt =" wkiol1ppsnlwpcb_aae1bhz5si0199.jpg "/>

Data can come from a database or text, for example:

Order = esproc. Query ("selectorderid, sellerid, amount, orderdate from sales ")

EMP = esproc. Query ("selecteid, name, gender, birthday, DEPT, salary from EMP ")

Dep = esproc. Query ("select * from Department ")

Set calculator code

A3 = order. Switch (sellerid, EMP: Eid)

A4 = order. New (orderid, amount, sellerid. Name, sellerid. Gender, sellerid. Salary)

Calculation Result

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/45/B1/wKiom1Ppr7qDC2R-AACOCpRxjCI465.jpg "Title =" esproc_foreign_key_2.jpg "style =" float: none; "alt =" wKiom1Ppr7qDC2R-AACOCpRxjCI465.jpg "/>

Code explanation

A3: Replace the sellerid in order with the corresponding record in EMP to establish a foreign key relationship between the two.

A4: obtains the orderid and amount fields in the order, and obtains the name, gender, and salary fields in EMP through association references. We can see that the field in EMP can be directly accessed from order with object reference, eliminating complicated and difficult join statements.

 

Example2:Follow the masterTable Condition query subtable

Find the orders signed by sales staff with a salary of more than 10000.

Code of the Set calculator:

A3 = order. Switch (sellerid, EMP: Eid)/same example

A5 = order. Select (sellerid. salary> 10000 & sellerid. Gender = "F ")

Calculation Result

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/45/B2/wKioL1PpsNPw_6iuAACa1LT8rds669.jpg "Title =" esproc_foreign_key_3.jpg "style =" float: none; "alt =" wkiol1ppsnpw_6iuaaca1lt8rds669.jpg "/>

Click the blue hyperlink to see the corresponding employee information:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/45/B1/wKiom1Ppr7ziIWXSAADDvlM9FWY263.jpg "Title =" esproc_foreign_key_4.jpg "style =" float: none; "alt =" wkiom1ppr7ziiwxsaaddvlm9fwy263.jpg "/>

Example3:Group by main table

Calculate the sales of each department.

Code of the Set calculator:

A3 = order. Switch (sellerid, EMP: Eid)/same example

A5 = order. Groups (sellerid. Dept; sum (amount ))

Calculation Result:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/45/B2/wKioL1PpsNWwxTZTAAB0pQpyz6c193.jpg "Title =" esproc_foreign_key_5.jpg "style =" float: none; "alt =" wkiol1ppsnwwxtztaab0pqpyz6c193.jpg "/>

You can rename a field, such as order. Groups (sellerid. dept: DT; sum (amount): AMT:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/45/B1/wKiom1Ppr76gJmbLAABuPBfOWHs262.jpg "Title =" esproc_foreign_key_6.jpg "style =" float: none; "alt =" wkiom1ppr76gjmblaabupbfowhs262.jpg "/>

 

Example4:Complex Multi-Table Association

For departments with sales exceeding 50000, find the name of the department manager.

Code of the Set calculator:

A3 = order. Switch (sellerid, EMP: Eid)

A4 = dep. Switch (Manager, EMP: Eid)

A5 = EMP. Switch (Dept, DEP: deptno)

A6 = order. Groups (sellerid. dept: DT; sum (amount): AMT)

A7 = a6.select (AMT <= 50000). (DT). (manager). (name)

Calculation Result:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/45/B2/wKioL1PpsNfzmuwUAAAd5ihTnzI463.jpg "Title =" esproc_foreign_key_7.jpg "style =" float: none; "alt =" wkiol1ppsnfzmuwuaaad5ihtnzi463.jpg "/>

Code explanation:

A3, A4, and A5: Establish a complete foreign key relationship.

A6: Calculate the sales of each department (see the previous example ). That is:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/45/B1/wKiom1Ppr7-SliMIAABs-enRAbA051.jpg "Title =" esproc_foreign_key_8.jpg "style =" float: none; "alt =" wKiom1Ppr7-SliMIAABs-enRAbA051.jpg "/>

A7: intuitive solution using object references. Expression a6.select (AMT <= 50000). (DT). (manager). (name) can be divided into four steps according to the period, namely:

1. Find records with sales exceeding 50000 from A6.

2. obtain the records corresponding to the DT field (in the DEP table ).

3. Obtain the record corresponding to the Manager field (in the EMP table ).

4. Obtain the name field.

The details are as follows:

A6.select (AMT <= 50000) 650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/45/B1/wKiom1Ppr8DyeSXDAAAs0CvWNP0200.jpg "Title =" esproc_foreign_key_9.jpg "style =" float: none; "alt =" wkiom1ppr8dyesxdaaas0cvwnp0200.jpg "/>

 

. (DT) 650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/45/B2/wKioL1PpsNnSwd1WAABEWju7zRc659.jpg "Title =" esproc_foreign_key_10.jpg "style =" float: none; "alt =" wkiol1ppsnnswd1waabewju7zrc659.jpg "/>

. (Manager) 650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/45/B1/wKiom1PpsOzxR1PjAABug3bn2mE956.jpg "Title =" esproc_foreign_key_11.jpg "style =" float: none; "alt =" wkiom1ppsozxr1pjaabug3bn2me956.jpg "/>

. (Name) 650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/45/B2/wKioL1PpsgWD0r2VAAAbRasHZqM316.jpg "Title =" esproc_foreign_key_12.jpg "style =" float: none; "alt =" wkiol1ppsgwd0r2vaaabrashzqm%.jpg "/>