Gray [6] cross tabulation CUBE, ROLL UP Johnson [7] pivoting SQL 3. SuperSQL SuperSQL SuperSQL SQL [1] [2] SQL SELECT GENERATE <media> <TFE> GENER- AT

Size: px

Start display at page:

Download "Gray [6] cross tabulation CUBE, ROLL UP Johnson [7] pivoting SQL 3. SuperSQL SuperSQL SuperSQL SQL [1] [2] SQL SELECT GENERATE <media> <TFE> GENER- AT"

えみおまた
5 years ago
Views:

1 DEIM Forum 2017 E3-1 SuperSQL {tabata,goto}@db.ics.keio.ac.jp, toyama@ics.keio.ac.jp,,,, SuperSQL SuperSQL, SuperSQL. SuperSQL 1. SuperSQL, Cross table, SQL, SuperSQL SuperSQL, SQL SQL SuperSQL, HTML, PDF, PHP HTML,, SuperSQL SuperSQL., SuperSQL,, 1 2 TFE, 2, 3 SuperSQL, 3 4, 5 2., Microsoft SQL server [4] SQL server PIVOT,, Microsoft Office Excel [5] SQL SQL,

2 Gray [6] cross tabulation CUBE, ROLL UP Johnson [7] pivoting SQL 3. SuperSQL SuperSQL SuperSQL SQL [1] [2] SQL SELECT GENERATE <media> <TFE> GENER- ATE <media> HTML PDF <TFE> Target Form Expression 3. 1 ( ) 3 (,) name, place name place (!) name name! place place 3. 2 [ ]!, [ ]!, [ ]! [! [, ]! ]! ([ ],) [Name], name1 name2 name10 ([ ]!) [Name]! 3. 3 name1 name2 name10 SuperSQL (asc) (desc) (asc)or(desc)< > 3. 4 SuperSQL (@) < >@{< >} =, [name@{width=100, color=red}]! 3. 5 SuperSQL SuperSQL ( 1, 2,...) SuperSQL image image image(pict,./pic ) link ( HTML ) link FOREACH link(name,./menu.ssql, place)

3 4.,,, 4. 1, SuperSQL TFE TFE,, SuperSQL 4 cross tab(tfe1, TFE2, TFE3)@{side-width=num, null-value= str } TFE 3.,, 3 4. 2 4. 1 cross tab TFE 3 1 TFE1 2 TFE2 3 TFE3 TFE3, TFE TFE 4.

3 3 4.,,, 4. 1, SuperSQL TFE TFE,, SuperSQL 4 cross tab(tfe1, TFE2, TFE3)@{side-width=num, null-value= str } TFE 3.,, cross tab TFE 3 1 TFE1 2 TFE2 3 TFE3 TFE3, TFE TFE 4. 3 SuperSQL , asc desc 5 cross tab([c.day![c.name],],, [p.performance]!, [(desc)s.id]!) s.id desc A, B, C 4. 1 null-value 6 N/A, 4. 5 TFE. 100px 4. 1 side-width

6 8 NULL TFE1 NULL 10, 11 7. 7 10px 10px, 4. 6, (4. 6. 2 ), (4. 6. 3 ), (4. 6. 4 ) 4. 6. 1 SuperSQL SSQL SQL (TFEtree) Parser, SQL Data Constructor, Code Generator 8, 4.

4 6 8 NULL TFE1 NULL 10, px 10px, 4. 6, ( ), ( ), ( ) SuperSQL SSQL SQL (TFEtree) Parser, SQL Data Constructor, Code Generator 8, SuperSQL 4.1 TFE, [TFE1],! [TFE2, [null(tfe1), TFE3]!]! 4.1, [TFE1],! [TFE2, [null(tfe1), TFE3]!]! Algorithm 1 TFE Input: cross tab TFE Output: TFE 1: cross tab TFE1 TFE3 2: while TFE1 do 3: if then 4: 5: end if 6: end while 7: while TFE1 do 8: NULL 9: end while 10: NULL TFE1 TFE3, TFE2 11: TFE1 12: return TFE 10 NULL TFE1 TFE3 TFE1 TFE3 TFE2 TFE2 TFE [TFE2, [null(tfe1), TFE3]!]! 11 TFE1 10 TFE

5 SuperSQL tfe anlysis Algorithm 2 Input: TFE Output: TFE 1: FUNCTION tfe anlysis(tfe) 2: if then 3: if then 4: return TFE 5: else 6: 7: return TFE 8: end if 9: else 10: TFE tmp 11: return tfe anlysis(tmp) 12: end if TFE 2, SuperSQL TFE [, [, [[ ], [ ]]]] TFE, [[female, male], [13, 14, 15]] [[DBS, DM, Webapp], [A, B, C]] combine 11 (A, B, C) Algorithm 3 Input: SQL Output: ( N/A ) 1: // 2: for all do 3: for all do 4: if then 5: 6: end if 7: end for 8: end for 9: // 10: combine(1, 1, 2,, ) 11: //SQL, 12: for all do 13: if then 14: 15: end if 16: end for 17: //SQL 18: for all do 19: for all do 20: if then 21: N/A 22: end if 23: end for 24: end for 25: return N/A 26: // combine 27: FUNCTION combine(num, 1, 2,, ) 28: for all 1 do 29: for all 2 do 30: 1 2 result 31: end for 32: end for 33: num++ 34: if num then 35: combine(num, result, num, 36: end if, ) 37: return result

9 A, B, C SSQL SQL 12 16 17 N/A 4. 7 student(id, name, gender, age, dept) class(id, name, day) performance(id, s id, c id, performance( ), score( )) student, class ( ), performance student class 4. 7. 1 1: Generate HTML cross tab([c.

6 9 A, B, C SSQL SQL N/A 4. 7 student(id, name, gender, age, dept) class(id, name, day) performance(id, s id, c id, performance( ), score( )) student, class ( ), performance student class : Generate HTML cross tab([c.day![c.name],],, [s.dept,[s.name]!]!, p.performance)@{side-width=240} FROM student s, class c, performance p WHERE s.id = p.s id AND c.id = p.c id, : A, B, C 10 A, B, C Generate HTML cross tab([s.gender! [s.dept],],, [c.name, [p.performance]!]!, count[s.id])@{side-width=120, null value= } FROM student s, class c, performance p WHERE s.id = p.s id AND c.id = p.c id

11 A, B, C, 11 4. 7. 3 3: Generate HTML cross tab([c.day! [c.name! avg[p.

7 11 A, B, C, : Generate HTML cross tab([c.day! [c.name! avg[p.score] ],],, [s.dept,[s.name]!]!, p.score)@{side-width=200} FROM student s, class c, performance p WHERE s.id = p.s id AND c.id = p.c id, { M, F }., member.gender E, I, ( ), ( ) S, T, F 12, M , I, F M, M,, IFM Microsoft SQL Server

8 ,, ES, ISM, IS Microsoft Excel Microsoft Excel. ITM IFM 5. 2,, 3,. SSQL, 1, TFE (SSQL ) (SSQL ) SSQL (SSQL ) 2 (SSQL ), (SSQL ) 3 GUI (SSQL ) 100px (SSQL ) cross table Where (SSQL ) [] (SSQL ) SuperSQL, SQL SuperSQL GUI SuperSQL SuperSQL SuperSQL. SuperSQL, px, [1] SuperSQL: [2] M. Toyama: SuperSQL: An Extended SQL for Database Publishing and Presentation Proceedings of ACM SIG- MOD 98 International Conference on Management of Data pp [3] Toshiyuki Seto Takuhiro Nagafuji Motomichi Toyama Generating HTML sources with TFE enhanced SQL SAC 97 Proceedings of the 1997 ACM symposium on Applied computing pp [4] Microsoft SQL Server : /ja-jp/library/ms aspx [5] Microsoft Excel : /download/0/0/5/005d55b9-82e2-489d-bb55-1b6b529f1b8f/ QuickGuide Excel2013.pdf [6] Jim Gray, Surajit Chaudhuri, Adam Bosworth, Andrew Layman, Don Reichart, Murali Venkatrao Data Cube: A Relational Aggregation Operator Generalizing Group-By, Cross-Tab, and Sub-Totals Data Mining and Knowledge Discovery 1, 2953 (1997) [7] Stephen B. Johnson, Damianos Chatziantoniou Extended SQL for Manipulating Clinical Warehouse Data Proc AMIA Symp. 1999:

, [! [, ]! ]!,,., ([ ],). : [Name], name1 name2 name10 ([ ]!). name1 name2 : [Name]! name SuperSQL,,,,,,, < < > } =.,

, [! [, ]! ]!,,., ([ ],). : [Name], name1 name2 name10 ([ ]!). name1 name2 : [Name]! name SuperSQL,,,,,,, < < > } =., DEIM Forum 2016 D4-4 SStest: SuperSQL 223-8522 3-14-1 E-mail: {rima,goto,masato}@db.ics.keio.ac.jp, toyama@ics.keio.ac.jp SuperSQL SQL SuperSQL,., SuperSQL,, (SStest). SStest GUI SuperSQL, SuperSQL. GUI,