DEIM Forum 2017 E3-1 SuperSQL 223 8522 3 14 1 E-mail: {tabata,goto}@db.ics.keio.ac.jp, toyama@ics.keio.ac.jp,,,, SuperSQL SuperSQL, SuperSQL. SuperSQL 1. SuperSQL, Cross table, SQL,. 1 1 2 4. 1 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,
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 3. 5. 1 image image image(pict,./pic ) 3. 5. 2 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 SuperSQL 4. 1 2, 5 3. 3 asc desc 5 cross tab([c.day![c.name],],, [p.performance]!, [(desc)s.id]!) s.id desc 4. 4 6 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. 6. 2 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
4. 6. 3 4.3 SuperSQL 4.3 1 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, 3 4.3 SuperSQL TFE [, [, [[ ], [ ]]]] TFE, 12 4. 6. 4 4.4 1. 9 [[female, male], [13, 14, 15]] [[DBS, DM, Webapp], [A, B, C]] 2 8 9 2 8 54 10 26 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.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,. 10 4. 7. 2 2: 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.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, 12 5. 5. 1 2. 5. 1. 1 { M, F }., member.gender E, I, ( ), ( ) S, T, F 12, M 5. 1. 2, I, F M, M,, IFM Microsoft SQL Server
,, 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 6. 6. 1 SuperSQL SuperSQL SuperSQL. SuperSQL, 6. 2 100px, [1] SuperSQL: http://supersql.db.ics.keio.ac.jp [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. 584-586 1998 [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. 96-100 1997 [4] Microsoft SQL Server : https://msdn.microsoft.com /ja-jp/library/ms130214.aspx [5] Microsoft Excel : http://download.microsoft.com /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:819-23.