ãã€ããªããèšèªã¢ããªã³ã°ã³ã³ããŒãã³ãã¯ãè«ççãªé¢ä¿ã«ãã£ãŠæ¥ç¶ããããªããžã§ã¯ããšããæŠå¿µã®ã»ããã§ããç¶æ¿ãæŠå¿µéã®é¢ä¿ã®å®çŸ©ãªã©ãæŠå¿µãå®çŸ©ããäž»ãªæ¹æ³ã«ã€ããŠè©±ãããšãã§ããŸããããŸããåŠå®æŒç®åãé«éè¿°èªè«çã®ã»ãã³ãã£ã¯ã¹ãªã©ãè«çããã°ã©ãã³ã°ã®åŸ®åŠãªéãã«ã€ããŠã説æããŸãããã®ãããã¯ã«é¢ããåºçç©ã®å®å šãªãªã¹ãã¯ããã®èšäºã®æåŸã«ãããŸãã
ããŒã¿ãæ±ãåéã§ã¯ãè°è«ã®äœå°ã®ãªããªãŒããŒã¯SQLèšèªã§ããéçŽãªã©ãå®éã«ã¯éåžžã«äŸ¿å©ã§ããããšãå€æãããã®æ©èœã®äžéšã¯ãåŸã§è«çããã°ã©ãã³ã°ã«ç§»è¡ãããŸããããããã£ãŠãã¢ããªã³ã°ã³ã³ããŒãã³ãã«ã€ããŠã¯ãSQLããå¯èœãªéãåçšãããšäŸ¿å©ã§ãããã®èšäºã§ã¯ããã¹ããããã¯ãšãªãå€éšçµåãããã³éèšãæŠå¿µå®çŸ©ã«åã蟌ãæ¹æ³ã玹ä»ããŸãããŸããè«çæ€çŽ¢ã«é Œããã«ã¢ã«ãŽãªãºã ã¹ã¿ã€ã«ã§ãªããžã§ã¯ãïŒãšã³ãã£ãã£ïŒãçæããé¢æ°ã䜿çšããŠèª¬æããããå¥ã®ã¿ã€ãã®æŠå¿µã«ã€ããŠã説æããŸãããŸããSQL UNNESTæäœãšã®é¡æšã«ããããªããžã§ã¯ãã®é åã芪ã®æŠå¿µãšããŠäœ¿çšããæ¹æ³ã瀺ããŸãã ã³ã¬ã¯ã·ã§ã³ãããŒãã«åœ¢åŒã«å€æããFROMå¥ã§ä»ã®ããŒãã«ã«çµåã§ããããã«ããŸã ã
æŠå¿µã®å¿åã®å®çŸ©
SQLã®äžçã§ã¯ããã¹ããããã¯ãšãªã¯ãã¡ã€ã³ã¯ãšãªã§ããã«åŠçããããã«äžéããŒã¿ãååŸããå¿ èŠãããå Žåã«ãã䜿çšãããããŒã«ã§ããã¢ããªã³ã°ã³ã³ããŒãã³ãã¯ãäžéããŒã¿ãååŸããæ¹æ³ãå¥ã®æŠå¿µãšããŠåœ¢åŒåã§ããããããã®ãããªç·æ¥ã®å¿ èŠæ§ã¯ãããŸããããã ãããã¹ããããæŠå¿µå®çŸ©ã䟿å©ãªå ŽåããããŸãã
å Žåã«ãã£ãŠã¯ãæŠå¿µãå°ãå€æŽããåã ã®å±æ§ãéžæããå€ãé€å€ããå¿ èŠããããŸãããã®å€æŽã1ã€ã®å Žæã§ã®ã¿å¿ èŠãªå Žåã¯ãäžæã®ååã§å¥ã®æŠå¿µãäœæããããšã¯æå³ããããŸããããã®ç¶æ³ã¯ãæŠå¿µãexistsã findããŸã㯠ãªã©ã®é¢æ°ãžã®åŒæ°ã§ããå Žåã«ããçºçã ãŸããfindOneã¯ãæŠå¿µã®æšè«å¯èœæ§ããã§ãã¯ããæŠå¿µã®æåã®ãªããžã§ã¯ãïŒãšã³ãã£ãã£ïŒã®ãã¹ãŠãŸãã¯ã®ã¿ãæ€çŽ¢ããŸããããã§ã¯ã次ã®ãããªé¢æ°ã®åŒæ°ãšããŠé »ç¹ã«äœ¿çšãããŠããé¢æ°åããã°ã©ãã³ã°èšèªã§å¿åé¢æ°ããšã®ã¢ãããžãŒãæãããšãã§ã ãããã æ€çŽ¢ã ãã£ã«ã¿ãªã©ã¯ã
å¿åã®æŠå¿µãå®çŸ©ããããã®æ§æãèããŠã¿ãŸããããäžè¬ã«ãããã¯äžè¬çãªæŠå¿µå®çŸ©ã®æ§æã«åŸããŸãããå±æ§ãªã¹ããšåæŠå¿µåãçç¥ã§ããå ŽåããããŸããå¿åã®æŠå¿µãååšããããã®åŒæ°ãšããŠäœ¿çšããã å Žåããã®å Žåããã®ååãšå±æ§ã®ãªã¹ãã¯éèŠã§ã¯ãããŸãããå°ãªããšãäœããã®çµæãããããšã確èªããã ãã§ååã§ãã æ€çŽ¢ãš findOneæ©èœã¯ãåºåãå šäœã®ã³ã³ã»ãããšããŠã§ã¯ãªããå¯äžã®é£æ³é åå ã®å±æ§ã®ã»ãããšããŠäœ¿çšãããŠããªãå ŽåãæŠå¿µåãå¿ èŠãšããªãå ŽåããããŸããå±æ§ãæå®ãããŠããªãå Žåãããã©ã«ãã§ç¶æ¿ã¡ã«ããºã ã䜿çšãããå±æ§ã¯èŠªã®æŠå¿µããç¶æ¿ãããŸããã³ã³ã»ããåãæå®ãããŠããªãå Žåã¯ãèªåçã«çæãããŸãã
ããã€ãã®äŸã䜿çšããŠãäžèšã§èšè¿°ãããå 容ã説æããŠã¿ãŸããããååšé¢æ°ã䜿çšã㊠ãåã蟌ãŸããæŠå¿µã®æšè«å¯èœæ§ãŸãã¯éæšè«å¯èœæ§ã確èªã§ããŸãã
concept freeExecutor is executor e where not exists ( task t where t.executor = e.id and t.status in ('assigned', 'in process') )
ãã®äŸã§ã¯ãå¿åã®æŠå¿µã¯æ¬¡ã®ãšããã§ãã
(task t where t.executor = e.id and t.status in ('assigned', 'in process'))
å®éã«ã¯ãã¿ã¹ã¯ã®æŠå¿µã®ãã¹ãŠã®å±æ§ãç¶æ¿ããæŠå¿µ ã§ãã
(concept _unanimous_task_1 as task t where t.executor = e.id and t.status in ('assigned', 'in process'))
æ€çŽ¢æ©èœã 䜿çšãããšãæŠå¿µã®ãã¹ãŠã®å€ããªã¹ããšããŠè¿ãããšãã§ããå±æ§ã«é¢é£ä»ããããšãã§ããŸãïŒ
concept customerOrdersThisYear is customer c with orders where c.orders = find( (id = o.id, status = o.status, createdDate = o.createdDate, total = o.total) from order o where o.customerId = c.id and o.createdDate > '2021-01-01' )
ãã®äŸã§ã¯ã泚æã®æŠå¿µã®éžæãããå±æ§ãå«ããªããžã§ã¯ãã§ãã泚æã®ãªã¹ãã䜿çšããŠã顧客ã®æŠå¿µãæ¡åŒµã ãŸã ãå¿åæŠå¿µã®å±æ§ã®ãªã¹ããæå®ããŸãããããã®ååã¯çç¥ãããŠããŸãã ã»ã¯ã·ã§ã³ã®æ¡ä»¶ å¿åã®æŠå¿µã¯ããã®ã±ãŒã¹ã§ã¯ã芪ãŸãã¯åäŒç€ŸæŠå¿µã®ä»ã®å±æ§ãå«ãããšã c.idãå¿åã®æŠå¿µã®ç¹åŸŽã¯ããã®ãããªãã¹ãŠã®å€éšå€æ°ãšå±æ§ã¯ããœãªã¥ãŒã·ã§ã³ã®æ€çŽ¢ãéå§ãããšãã«å¿ ãå€ã«é¢é£ä»ããããŠããå¿ èŠãããããšã§ãããããã£ãŠãå¿åã®æŠå¿µã®ãªããžã§ã¯ãã¯ã顧客ã®æŠå¿µã®ãªããžã§ã¯ããèŠã€ããåŸã§ã®ã¿èŠã€ããããšãã§ããŸãã
..ã
å€éšæ¥ç¶
å¿åã®æŠå¿µå®çŸ©ã¯ã芪æŠå¿µãè¡šãfromã»ã¯ã·ã§ã³ã§ã䜿çšã§ã ãŸããããã«ãå¿åã®æŠå¿µã®å®çŸ©ã§ã¯ããããä»ã®æŠå¿µãšçµã³ä»ããæ¡ä»¶ã®ããã€ãã転éããããšãå¯èœã§ãããããã¯ç¹å¥ãªå¹æããããããŸãããããã®æ¡ä»¶ã¯ãå¿åã®æŠå¿µã®è§£æ±ºçãèŠã€ãã段éã§ãã§ãã¯ãããåã®æŠå¿µã®æšè«ããã»ã¹ã«ã¯åœ±é¿ããŸãããããã§ã¯ãå¿åæŠå¿µã®whereã»ã¯ã·ã§ã³ã®æ¡ä»¶ ãšSQLã®JOINONã»ã¯ã·ã§ã³ã®æ¡ä»¶ã®é¡äŒŒç¹ãæãããšãã§ã ãŸãã
ãããã£ãŠãå¿åã®æŠå¿µã䜿çšããŠãå·Šå€éšçµåã®SQLã¢ããã°ãå®è£ ã§ããŸããããã«ã¯3ã€ã®ããšãå¿ èŠã§ãã
- ãŸããç®çã®èŠªæŠå¿µãããã«åºã¥ãå¿åã®æŠå¿µã«çœ®ãæããä»ã®èŠªæŠå¿µãšã®ãã¹ãŠã®æ¥ç¶ãããã«è»¢éããŸãã
- 第äºã«ããã®æŠå¿µã®æšè«ã®å€±æããåã®æŠå¿µå šäœã®æšè«ã®èªåçãªå€±æã«ã€ãªããã¹ãã§ã¯ãªãããšãææããããšããããè¡ãã«ã¯ããã®èŠªã®æŠå¿µããªãã·ã§ã³ã®ããŒã¯ãŒãã§ããŒã¯ããå¿ èŠããããŸãã
- ãããŠç¬¬3ã«ãåã®æŠå¿µã®whereã»ã¯ã·ã§ã³ã§ããã®å¿åã®æŠå¿µã«å¯Ÿãã解決çããããã©ããã確èªã§ããŸãã
å°ããªäŸãèŠãŠã¿ãŸãããïŒ
concept taskAssignedTo (task = t, assignee = u, assigneeName) from task t, optional (user where id = t.assignedTo) u where assigneeName = if(defined(u), u.firstName + ' ' + u.lastName, 'Unassigned')
taskAssignedToã³ã³ã»ããã®å±æ§ã« ã¯ãã¿ã¹ã¯ã®ãªããžã§ã¯ãããã®ãšã°ãŒãã¥ãŒã¿ãŒãããã³åå¥ã«ãšã°ãŒãã¥ãŒã¿ãŒã®ååãå«ãŸããŸãã芪ã®æŠå¿µã¯ ã¿ã¹ã¯ãš ãŠãŒã¶ãŒã§ãããã¿ã¹ã¯ã«ãŸã ãšã°ãŒãã¥ãŒã¿ãŒããªãå ŽåãåŸè ã¯ç©ºã«ããããšãã§ããŸããããã¯ããªãã·ã§ã³ã®ããŒã¯ãŒããåã«ä»ããå¿åã®æŠå¿µå®çŸ©ã§ã©ãããããŠã ãŸããæšè«ããã·ãŒãžã£ã¯ãæåã«ã¿ã¹ã¯ã³ã³ã»ããã®ãªããžã§ã¯ããæ€çŽ¢ã ã次ã«ãŠãŒã¶ãŒã«åºã¥ã㊠å¿åã³ã³ã»ãããäœæããã¿ã¹ã¯ã³ã³ã»ããã® assignedToå±æ§ã®ç¹å®ã®å€ã«é¢é£ä»ã ãŸããããŒã¯ãŒã ãªãã·ã§ã³ã¯ãæŠå¿µã倱æããå Žåããã®ãªããžã§ã¯ããç¹å¥ãªå€UNDEFINEDã«é¢é£ä»ããããããšãæšè«ã«ãŒãã³ã« éç¥ããŸãããŸããåã®æŠå¿µã¬ãã«ã§åºåã®çµæã確èªãããšãassigneeNameå±æ§ã§ããã©ã«ãå€ãèšå®ã§ã ãŸãããªãã·ã§ã³ã®ããŒã¯ãŒããæå®ãããŠããªãå Žåã å¿åã®æŠå¿µãæšæž¬ã§ããªããšãåã®æŠå¿µæ€çŽ¢ã®çŸåšã®ãã©ã³ãã倱æããŸããããã¯ãSQLã®å éšçµåã«é¡äŒŒããŠããŸãã å¿åæŠå¿µã®where
å¥ã®æ¡ä»¶ã« ã¯ãä»ã®èŠªæŠå¿µã® assignedToå±æ§ãå«ãŸããŠããããã§ãã ã¿ã¹ã¯ã®å Žåãã³ã³ã»ãããŠãŒã¶ãŒã®ãªããžã§ã¯ãã®æ€çŽ¢ã¯ã ã¿ã¹ã¯ãªããžã§ã¯ããå€ã§ãã€ã³ãããåŸã§ã®ã¿å¯èœ ã§ãããããã亀æããããšã¯ã§ããŸããïŒ
from optional (user where id = t.assignedTo) u, task t
åæ段éã§ã¯t.assignedToã®å€ã¯ äžæã§ãããããå¿åã®æŠå¿µã®å®çŸ©ãäœæããããšã¯ã§ããŸããã
SQLã®å Žåãfromã»ã¯ã·ã§ã³ã®ããŒãã«ã®é åº éèŠã§ã¯ãããŸãããPrologã§ã¯ãã«ãŒã«å ã®è¿°èªã®é åºã«ãã£ãŠã決å®æšããã©ããŒã¹ããé åºãäžæã«æ±ºå®ãããŸããã·ãã¥ã¬ãŒã·ã§ã³ã³ã³ããŒãã³ãã«ã€ããŠãåãããšãèšããŸããã·ãã¥ã¬ãŒã·ã§ã³ã³ã³ããŒãã³ãã®åºåã«ãŒã«ã¯ãPrologã§äœ¿çšãããSLD解å床ã«åºã¥ããŠããŸãããã®äžã§ãå·ŠåŽã®æŠå¿µã®ãªããžã§ã¯ãã®åºåã®çµæã¯ãå³åŽã®ãªããžã§ã¯ãã®åºåã®å¶éã決å®ããŸãããã®ãããæ®å¿µãªãããå³å€éšçµåãšå®å šå€éšçµåã®æäœãåãèªç¶ãªæ¹æ³ã§å®è£ ããããšã¯ã§ããŸãããå³ã®èŠªæŠå¿µã®äžé£ã®çµæã®ã«ãŒãã£ããªãã£ã¯ãå·Šã®æŠå¿µã®çµæããã倧ãããªãå¯èœæ§ããããããå³ã®æŠå¿µããå·Šãžã®å察æ¹åã«åºåããå¿ èŠããããŸããæ®å¿µãªãããéžæããæšè«æé ã®ç¹æ§ã«ãããèšèªã®æ©èœã«å¶éã課ããããŸãããã ããå®å šãªå€éšçµåæäœã¯ãå éšãçµåããããšã§ãšãã¥ã¬ãŒãã§ããŸããå·Šå³ã®çµåïŒ
concept outerJoinRelation( concept1Name, concept2Name, concept1Key, concept2Key, concept1 = c1, concept2 = c2 ) from <concept1Name> c1, <concept2Name> c2 where c1.<concept1Key> = c2.<concept2Key>; concept outerJoinRelation( concept1Name, concept2Name, concept1Key, concept2Key, concept1 = c1, concept2 = null ) from <concept1Name> c1 where not exists( <concept2Name> c2 where c1.<concept1Key> = c2.<concept2Key>); concept outerJoinRelation( concept1Name, concept2Name, concept1Key, concept2Key, concept1 = null, concept2 = c2 ) from <concept2Name> c2 where not exists( <concept1Name> c1 where c1.<concept1Key> = c2.<concept2Key>);
ãã®äžè¬åãããæŠå¿µã¯ãåã®èšäºã§èª¬æããé«éè«çã䜿çšããŠèª¬æãããŠã ãŸãããã®å®çŸ©ã¯3ã€ã®éšåã«åãããŠããŸããæåã®ãã®ã¯æŠå¿µã®äº€å·®ç¹ãèŠã€ãã2çªç®ã¯-å·Šã®æŠå¿µãæã£ãŠãããªããžã§ã¯ããšæã£ãŠããªããªããžã§ã¯ãããããŠ3çªç®ã¯-ãã®éã§ããåããŒãã®ååã¯åãã§ããããããããã®æšè«ã®çµæãçµã¿åããããŸãã
éçŽ
éçŽã¯ãé¢ä¿ä»£æ°ãšè«çããã°ã©ãã³ã°ã®äž¡æ¹ã®äžå¯æ¬ ãªéšåã§ãã SQLã§ã¯ã GROUP BYå¥ã䜿çšãããšãåãããŒå€ãæã€è¡ããµããªãŒè¡ã«ã°ã«ãŒãåã§ããŸããéè€ããå€ãåé€ããããšãã§ããsumã countã minã maxã avgãªã©ã®éèšé¢æ°ã§äžè¬çã«äœ¿çšãã ãŸãã..ãè¡ã®ã°ã«ãŒãããšã«ãéèšé¢æ°ã¯ãã®ã°ã«ãŒãå ã®ãã¹ãŠã®è¡ã«åºã¥ããŠéåžžã®å€ãè¿ããŸããè«çããã°ã©ãã³ã°ã§ã¯ãéçŽã®ã»ãã³ãã£ã¯ã¹ã¯ããè€éã§ããããã¯ãSLDã«ãŒã«ã®ååž°çå®çŸ©ã®å Žåã«ãã£ãŠã¯ã解決ãç¡éã«ãŒãã«å ¥ããå®äºã§ããªããšããäºå®ã«ãããã®ã§ãã倱æãšããŠã®æåŠã®å Žåãšåæ§ã«ãéçŽæäœã§ã®ååž°ã®åé¡ã¯ãæ°žç¶çãªã¢ãã«ã»ãã³ãã£ã¯ã¹ãŸãã¯ååã«æ ¹æ ã®ããã»ãã³ãã£ã¯ã¹ã䜿çšããŠè§£æ±ºãããŸããååã®èšäºã§ããããã®ã¢ãããŒãã«ã€ããŠç°¡åã«èª¬æããããšããŸãã ããã ããã¢ããªã³ã°ã³ã³ããŒãã³ãã®ã»ãã³ãã£ã¯ã¹ã¯å¯èœãªéãåçŽã«ããå¿ èŠããããããæšæºã®SLD解å床ãæšå¥šãããŸãããããŠãç¡éååž°ãåé¿ãããšããåé¡ã¯ãæŠå¿µéã®æ¥ç¶ãå圢æããããšã«ãã£ãŠãããã解決ãããŸãã
éçŽã¯ããã€ããªããèšèªã®èšç®ã³ã³ããŒãã³ãã䜿çšããŠãæ©èœçãªã¹ã¿ã€ã«ã§èªç¶ã«å®è£ ã§ããŸãããããè¡ãã«ã¯ãæšè«çµæãäžæã®ã°ã«ãŒãã«ãŸãšããããããã®éèšé¢æ°ãèšç®ããé¢æ°ã§ååã§ããããããæŠå¿µã®å®çŸ©ãè«ççãªéšåãšæ©èœçãªéšåã«åå²ããããšã¯ãéçŽãªã©ã®éèŠãªããŒã«ã«ãšã£ãŠæã䟿å©ãªãœãªã¥ãŒã·ã§ã³ã§ã¯ãããŸãããå®çŸ©ã®æ§æãæ¡åŒµããŠãã°ã«ãŒãåã»ã¯ã·ã§ã³ãšéèšé¢æ°ãå«ããããšããå§ãããŸãã
concept < > < > ( < > = <>, ... ) group by < >, ... from < > < > ( < > = <> , ... ), ... where < >
SQLã®å Žåãšåæ§ã«ãgroup byã»ã¯ã·ã§ã³ ã«ã¯ãã°ã«ãŒãåãå®è¡ããããã®å±æ§ã®ãªã¹ããå«ãŸããŠããŸããé¢ä¿åŒã«ã¯ãéèšé¢æ°ãå«ããããšãã§ããŸãããã®ãããªé¢æ°ãå«ãåŒã¯ããã¹ãŠã®èŠªæŠå¿µã®å€ãèŠã€ãããã°ã«ãŒãåãå®è¡ããããŸã§ãæªå®çŸ©ãšèŠãªãããŸãã次ã«ããããã®å€ãã°ã«ãŒãããšã«èšç®ããå±æ§ã«é¢é£ä»ããããã°ã«ãŒãã®ãã£ã«ã¿ãªã³ã°ã«äœ¿çšãããã§ããŸããæ¡ä»¶ãè©äŸ¡ããã³ãã§ãã¯ããããã®ãã®æ æ°ãªã¢ãããŒãã§ã¯ãã°ã«ãŒãåã®ååŸã«ãã£ã«ã¿ãŒæ¡ä»¶ãåé¢ããHAVINGã»ã¯ã·ã§ã³ã¯å¿ èŠãããŸãã ãã©ã³ã¿ã€ã ã¯ãããèªåçã«è¡ããŸãã
äž»ãªéèšé¢æ°ã¯ã ã«ãŠã³ãã åèšã§ããã å¹³åã æå°ã æ倧ãé¢æ°ã®ç®çã¯ããã®ååããç解ã§ããŸããã¢ããªã³ã°ã³ã³ããŒãã³ãã¯è€åããŒã¿åã§èªç¶ã«æ©èœãããããã°ã«ãŒãåãããå€ããªã¹ããšããŠè¿ãé¢æ°ãè¿œå ããããšãã§ããŸãããããã°ã«ãŒããšåŒã³ãŸããã ããã®å ¥ååŒæ°ã¯åŒã§ãããã®é¢æ°ã¯ãã°ã«ãŒãå ã®åèŠçŽ ã«ã€ããŠãã®åŒãè©äŸ¡ããçµæã®ãªã¹ããè¿ããŸããä»ã®é¢æ°ãšçµã¿åãããããšã§ãä»»æã®éèšé¢æ°ãå®è£ ã§ããŸããã°ã«ãŒãé¢æ° ã¯ãgroup_concatã json_arrayagãªã©ã®SQLé¢æ°ãããäŸ¿å© ã§ããããã¯ããã£ãŒã«ãå€ã®é åãååŸããããã®äžéã¹ããããšããŠãã䜿çšãããŸãã
ã°ã«ãŒãåã®äŸïŒ
concept totalOrders ( customer = c, orders = group(o), ordersTotal = sum(o.total) ) group by customer from customer c, order o where c.id = o.customerId and ordersTotal > 100
orderså±æ§ ã«ã¯ããã¹ãŠã®ãŠãŒã¶ãŒæ³šæã®ãªã¹ããordersTotalïŒãã¹ãŠã®æ³šæã®åèšïŒãå«ãŸããŸã ãã°ã«ãŒãåãå®äºããåèšé¢æ°ãèšç®ãããåŸãordersTotal> 100æ¡ä»¶ ããã§ãã¯ãããŸã ã
æ©èœã«ãã£ãŠå®çŸ©ãããæŠå¿µ
æŠå¿µãèšè¿°ãã宣èšåã®è«ç圢åŒã¯ãå¿ ããã䟿å©ã§ã¯ãããŸãããèšç®ã®ã·ãŒã±ã³ã¹ãèšå®ããæ¹ã䟿å©ãªå Žåãããããã®çµæãæŠå¿µã®æ¬è³ªã«ãªããŸãããã®ç¶æ³ã¯ãããŒã¿ããŒã¹ããã¡ã€ã«ãå€éšãµãŒãã¹ãžã®ãªã¯ãšã¹ãã®éä¿¡ãªã©ãå€éšããŒã¿ãœãŒã¹ãããã¡ã¯ããããŒãããå¿ èŠãããå Žåã«ããçºçããŸããå ¥åã¯ãšãªãããŒã¿ããŒã¹ãžã®ã¯ãšãªã«å€æãããã®ã¯ãšãªã®å®è¡çµæãè¿ãé¢æ°ã®åœ¢åŒã§æŠå¿µãè¡šããšäŸ¿å©ã§ããè«ççãªçµè«ãæŸæ£ããç¹å®ã®åé¡ã®è©³çŽ°ãèæ ®ããŠããå¹ççã«è§£æ±ºããç¹å®ã®å®è£ ã«çœ®ãæããããšãçã«ããªã£ãŠããå ŽåããããŸãããŸããæŽæ°ã®ã·ãŒã±ã³ã¹ãªã©ãæŠå¿µãšã³ãã£ãã£ãçæããç¡éã®ã·ãŒã±ã³ã¹ãæ©èœçãªã¹ã¿ã€ã«ã§èšè¿°ããæ¹ã䟿å©ã§ãã
ãã®ãããªæŠå¿µãæ±ãååã¯ãäžèšã®ä»ã®æŠå¿µãšåãã§ããå¿ èŠããããŸãããœãªã¥ãŒã·ã§ã³ã®æ€çŽ¢ã¯ãåãæ¹æ³ã§éå§ããå¿ èŠããããŸãããããèªäœãæŠå¿µå®çŸ©ã®èŠªæŠå¿µãšããŠäœ¿çšã§ããŸãããœãªã¥ãŒã·ã§ã³ã®æ€çŽ¢ã®å éšå®è£ ã®ã¿ãç°ãªãå¿ èŠããããŸãããããã£ãŠãé¢æ°ã䜿çšããŠæŠå¿µãå®çŸ©ããå¥ã®æ¹æ³ã玹ä»ããŸãã
concept < > ( < >, ... ) by < >
é¢æ°ã䜿çšããŠå®çŸ©ãããæŠå¿µãå®çŸ©ããã«ã¯ããã®å±æ§ã®ãªã¹ããšãªããžã§ã¯ããçæããããã®é¢æ°ãæå®ããå¿ èŠããããŸããå±æ§ã®ãªã¹ããå®çŸ©ã®å¿ é èŠçŽ ã«ããããšã«ããŸãããããã«ããããã®ãããªæŠå¿µã®äœ¿çšãç°¡åã«ãªãããã§ãããã®æ§é ãç解ããããã«ããªããžã§ã¯ããçæããæ©èœã調ã¹ãå¿ èŠã¯ãããŸããã
ããã§ã¯ããªããžã§ã¯ããçæããããã®é¢æ°ã«ã€ããŠèª¬æããŸããããæããã«ãå ¥åãšããŠãªã¯ãšã¹ããåãåãå¿ èŠããããŸã-å±æ§ã®åæå€ããããã®å€ã¯æå®ããããšãæå®ããªãããšãã§ããããã䟿å®äžãé¢æ°ã®å ¥ååŒæ°ãšãªãé£æ³é åã«é 眮ã§ããŸããæŠå¿µã®æå³ã®æ€çŽ¢ãã©ã®ã¢ãŒãã§éå§ãããããç¥ãããšã圹ç«ã¡ãŸã-ãã¹ãŠã®å¯èœãªå€ãèŠã€ããããæåã®ãã®ã ããèŠã€ãããããŸãã¯è§£æ±ºçã®ååšã ãããã§ãã¯ããŸãããããã£ãŠã2çªç®ã®å ¥ååŒæ°ãšããŠæ€çŽ¢ã¢ãŒããè¿œå ããŸãã
é¢æ°ãè©äŸ¡ããçµæã¯ãæŠå¿µãªããžã§ã¯ãã®ãªã¹ãã«ãªããŸãããã ããããã¯ãã©ããã³ã°ã䜿çšããæ€çŽ¢ã«åºã¥ãæšè«æé ã§ã¯ããããã®å€ãäžåºŠã«1ã€ãã€æ¶è²»ããããããé¢æ°ã®åºååŒæ°ããªã¹ãèªäœã§ã¯ãªãããã®ã€ãã¬ãŒã¿ãŒã«ããããšãã§ããŸããããã«ãããæŠå¿µã®å®çŸ©ãããæè»ã«ãªããŸããããšãã°ãå¿ èŠã«å¿ããŠãé 延è©äŸ¡ãŸãã¯ãªããžã§ã¯ãã®ç¡éã·ãŒã±ã³ã¹ãå®è£ ã§ããŸããæšæºã³ã¬ã¯ã·ã§ã³ã®ã€ãã¬ãŒã¿ã䜿çšããããšããç¬èªã®ã«ã¹ã¿ã å®è£ ãäœæããããšãã§ããŸããã³ã¬ã¯ã·ã§ã³èŠçŽ ã¯ãã³ã³ã»ããå±æ§ã®å€ãæã€é£æ³é åã§ããå¿ èŠããããŸããã³ã³ã»ããã®ãšãã»ã³ã¹ã¯ããããã«åºã¥ããŠèªåçã«äœæãããŸãã
æ»ãå€ã®åãšããŠã€ãã¬ãŒã¿ã䜿çšããããšã«ã¯æ¬ ç¹ããããŸããåã«çµæã®ãªã¹ããè¿ããããé¢åã§ãŠãŒã¶ãŒãã¬ã³ããªãŒã§ã¯ãããŸãããæ±çšæ§ãã·ã³ãã«ãã䜿ãããããå Œãåããæé©ãªãªãã·ã§ã³ãèŠã€ããããšã¯ãå°æ¥ã®èª²é¡ã§ãã
äŸãšããŠãæéééã説æããæŠå¿µãèããŠã¿ãŸããããå°±æ¥æ¥ã15åééã«åå²ããããšããŸããããã¯ãããªãåçŽãªé¢æ°ã§å®è¡ã§ããŸãã
concept timeSlot15min (id, hour, minute) by function(query, mode) { var timeSlots = []; var curId = 1; for(var curHour = 8; curHour < 19; curHour += 1) { for(var curMinute = 0; curMinute < 60; curMinute += 15) { timeSlots.push({ id: curId, hour: curHour, minute: curMinute; }); curId++; } } return timeSlots.iterator(); }
ãã®é¢æ°ã¯ãå¶æ¥æ¥ã®15åééã®ãã¹ãŠã®å¯èœãªå€ã®ã€ãã¬ãŒã¿ãŒãè¿ããŸããããšãã°ããŸã äºçŽãããŠããªã空ãã¹ããããæ€çŽ¢ããããã«äœ¿çšã§ããŸãã
concept freeTimeSlot is timeSlot15min s where not exists (bookedSlot b where b.id = s.id)
ãã®é¢æ°ã¯ãã¯ãšãªã¯ãšãªã«æºæ ããŠãããã©ããã®èšç®çµæããã§ãã¯ããŸãã ãããã¯ãå±æ§ã®é åããšã³ãã£ãã£ã«å€æãããšãã«èªåçã«è¡ãããŸãããã ããå¿ èŠã«å¿ããŠãã¯ãšãªãã£ãŒã«ãã䜿çšããŠé¢æ°ãæé©åã§ããŸããããšãã°ãæŠå¿µã®ã¯ãšãªãã£ãŒã«ãã«åºã¥ããŠããŒã¿ããŒã¹ã¯ãšãªãäœæããŸãã
é¢æ°ãä»ããæŠå¿µã¯ãè«ççã»ãã³ãã£ã¯ã¹ãšæ©èœçã»ãã³ãã£ã¯ã¹ãçµã¿åããããã®ã§ããé¢æ°åãã©ãã€ã ã§é¢æ°ãå ¥ååŒæ°ã®æå®ãããå€ã®çµæãèšç®ããå Žåãè«çãã©ãã€ã ã§ã¯åºååŒæ°ãšå ¥ååŒæ°ã«åå²ãããŸãããåŒæ°ã®äžéšã®ã¿ãä»»æã®çµã¿åããã§æå®ã§ããé¢æ°ã¯æ®ãã®åŒæ°ã®å€ãèŠã€ããå¿ èŠããããŸããå®éã«ã¯ãä»»æã®æ¹åã§èšç®ãå®è¡ã§ãããã®ãããªé¢æ°ãå®è£ ã§ãããšã¯éããªããããèªç±åŒæ°ã®å¯èœãªçµã¿åãããå¶éããããšã¯çã«ããªã£ãŠããŸããããšãã°ãé¢æ°ãè©äŸ¡ããåã«ãããã€ãã®åŒæ°ãå€ã«ãã€ã³ãããå¿ èŠãããããšã宣èšããŸãããããè¡ãã«ã¯ãæŠå¿µã®å®çŸ©ã§ãã®ãããªå±æ§ãããŒã¯ãŒãrequiredã§ããŒã¯ããŸã ã
äŸãšããŠãç¹å®ã®ææ°ã¹ã±ãŒã«ã®å€ãå®çŸ©ããæŠå¿µãèããŠã¿ãŸãããã
concept expScale (value, position, required limit) by function(query, mode) { return { _curPos = 0, _curValue = 1, next: function() { if(!this.hasNext()) { return null; } var curItem = {value: this._curValue, position: this._curPosition, limit: query.limit}; this._curPos += 1; this._curValue = this._curValue * Math.E; return curItem; }, hasNext: function() { return query.limit == 0 || this._curPos < query.limit; } }}
ãã®é¢æ°ã¯ãé 延è©äŸ¡ã䜿çšããŠæŠå¿µãšã³ãã£ãã£ãçæããã€ãã¬ãŒã¿ãè¿ããŸããã·ãŒã±ã³ã¹ã®ãµã€ãºã¯limitå±æ§ã®å€ã«ãã£ãŠå¶éãããŸããã ãŒãã®å Žåã¯ç¡é倧ã«ãªããŸããç¡éã·ãŒã±ã³ã¹ã«åºã¥ãæŠå¿µã¯ãæšè«ã«ãŒãã³ãå®äºããããšãä¿èšŒããªããããéåžžã«æ³šææ·±ã䜿çšããå¿ èŠããããŸããlimitå±æ§ ã¯æ¬è³ªçã«è£å©çãªãã®ã§ãããèšç®ãæŽçããããã«äœ¿çšãããŸããä»ã®å±æ§ã®å€ããæšæž¬ããããšã¯ã§ããŸãããèšç®ãéå§ããåã«ç¥ã£ãŠããå¿ èŠããããããå¿ é ãšããŠããŒã¯ãããŸãã
é¢æ°ãšããŠã®æŠå¿µãã©ã®ããã«èŠãããã«ã€ããŠã®ãªãã·ã§ã³ã®1ã€ãæ€èšããŸããããããããã®ãããªæŠå¿µã®å®å šæ§ãšäœ¿ããããã®åé¡ã¯ãå°æ¥ããã詳现ãªç 究ãå¿ èŠãšããŸãã
ãã¹ããããã³ã¬ã¯ã·ã§ã³ã®ãã©ããå
ãªããžã§ã¯ã圢åŒã®ããŒã¿ãåŠçã§ããäžéšã®SQLãã€ã¢ã¬ã¯ãã¯ãã³ã¬ã¯ã·ã§ã³ã®å 容ãããŒãã«åœ¢åŒïŒè¡ã»ããïŒã«å€æããçµæã®ããŒãã«ãFROMå¥ã«è¿œå ãã UNNESTãªã©ã®æäœããµããŒãããŸã ãããã¯éåžžããã¹ããããæ§é ãæã€ãªããžã§ã¯ãããã©ããåãããããã€ãŸããªããžã§ã¯ãããã©ããåãŸãã¯ãã©ããåããããã«äœ¿çšãããŸãããã®ãããªèšèªã®äŸã¯ãBigQueryãSQL ++ã§ãã
ãŠãŒã¶ãŒæ å ±ãJSONãªããžã§ã¯ããšããŠä¿åãããšããŸãã
[ { "id":1, "alias":"Margarita", "name":"MargaritaStoddard", "nickname":"Mags", "userSince":"2012-08-20T10:10:00", "friendIds":[2,3,6,10], "employment":[{ "organizationName":"Codetechno", "start-date":"2006-08-06" }, { "organizationName":"geomedia", "start-date":"2010-06-17", "end-date":"2010-01-26" }], "gender":"F" }, { "id":2, "alias":"Isbel", "name":"IsbelDull", "nickname":"Izzy", "userSince":"2011-01-22T10:10:00", "friendIds":[1,4], "employment":[{ "organizationName":"Hexviafind", "startDate":"2010-04-27" }] }, âŠ]
ãŠãŒã¶ãŒãªããžã§ã¯ãã¯ãå人ãè·å Žã®ãªã¹ããšãšãã«ãã¹ããããã³ã¬ã¯ã·ã§ã³ãæ ŒçŽããŸããSQL ++ã¯ãšãªã䜿çšããŠããªããžã§ã¯ãã®äžäœã¬ãã«ããååŸãããŠãŒã¶ãŒã«é¢ããããŒã¿ãšäžç·ã«æ¥çããããšã«ããããŠãŒã¶ãŒã®è·å Žã«é¢ããæ·»ä»æ å ±ãæœåºããããšãã§ããŸãã
SELECT u.id AS userId, u.name AS userName, e.organizationName AS orgName FROM Users u UNNEST u.employment e WHERE u.id = 1;
çµæã¯æ¬¡ã®ããã«ãªããŸãã
[ { "userId": 1, "userName": "MargaritaStoddard", "orgName": "Codetechno" }, { "userId": 1, "userName": "MargaritaStoddard", "orgName": "geomedia" } ]
ãã®æäœã«ã€ããŠã¯ãããã§è©³ãã説æã ãŸãã
SQLãšã¯ç°ãªããã¢ããªã³ã°ã³ã³ããŒãã³ãã§ã¯ãåã蟌ãŸããããŒã¿ã衚圢åŒã§ã¯ãªããªããžã§ã¯ã圢åŒã«å€æããå¿ èŠããããŸããäžã§èª¬æããæŠå¿µã¯ããããæ¯æŽããŸã-é¢æ°ãšå¿åã®æŠå¿µã«ãã£ãŠå®çŸ©ãããæŠå¿µãé¢æ°ãä»ããæŠå¿µã䜿çšãããšããã¹ããããã³ã¬ã¯ã·ã§ã³ããªããžã§ã¯ã圢åŒã«å€æã§ããŸããå¿åã®æŠå¿µã䜿çšãããšããã®å®çŸ©ã芪æŠå¿µã®ãªã¹ãã«åã蟌ã¿ãç®çã®ãã¹ããããã³ã¬ã¯ã·ã§ã³ãå«ãå±æ§ã®å€ã«ã¢ã¯ã»ã¹ã§ããŸãã
é¢æ°ãä»ããæŠå¿µã®å®å šãªå®çŸ©ã¯ãå¿åã®æŠå¿µãšããŠäœ¿çšããã«ã¯é¢åãããããã次ã®ããã«ãªããŸãã
concept conceptName(attribute1, attribute2, ...) by function(query, mode) {...}
ãããçãããæ¹æ³ãèŠã€ããå¿ èŠããããŸãããŸããã¯ãšãªãã©ã¡ãŒã¿ãš ã¢ãŒããã©ã¡ãŒã¿ã䜿çšããŠãé¢æ°å®çŸ©ã®ã¿ã€ãã«ãåé€ã§ããŸã ã芪ã³ã³ã»ããã®äœçœ®ã§ã¯ãmodeåŒæ° ã¯åžžã«ããã¹ãŠã®ã³ã³ã»ããå€ãæ€çŽ¢ãã«ãªããŸããä»ã®æŠå¿µã®å±æ§ãžã®äŸåé¢ä¿ãé¢æ°ã®æ¬äœã«åã蟌ãããšãã§ãããããã¯ãšãªåŒæ° ã¯åžžã«ç©ºã«ãªããŸããã³ã³ã»ããããŒã¯ãŒããåé€ã§ããŸãããããã£ãŠã次ã®ããã«ãªããŸãã
conceptName(attribute1, attribute2, ...) {âŠ}
æŠå¿µã®ååãéèŠã§ãªãå Žåã¯ãçç¥ã§ããèªåçã«çæãããŸãã
(attribute1, attribute2, ...) {âŠ}
å°æ¥ãé¢æ°ã«ãã£ãŠè¿ããããªããžã§ã¯ãã®ã¿ã€ãããå±æ§ã®ãªã¹ããæšæž¬ã§ããã³ã³ãã€ã©ãäœæã§ããå Žåã¯ãå±æ§ã®ãªã¹ããç Žæ£ã§ããŸãã
{âŠ}
ãããã£ãŠããŠãŒã¶ãŒãšãã®è·å ŽãæŠå¿µãšããŠäœ¿çšããäŸã¯ã次ã®ããã«ãªããŸãã
concept userEmployments ( userId = u.id, userName = u.name, orgName = e.orgName ) from users u, {u.employment.map((item) => {orgName: item.organizationName}).iterator()} e
解決çã¯å°ãåé·ã§ãããæ®éçã§ããããšãå€æããŸãããåæã«ããã¹ããããã³ã¬ã¯ã·ã§ã³ã®ãªããžã§ã¯ãã®å€æãå¿ èŠãªãå Žåã¯ãå€§å¹ ã«ç°¡ç¥åã§ããŸãã
concept userEmployments ( userId = u.id, userName = u.name, orgName = e. organizationName ) from users u, {u.employment.iterator()} e
調æ»çµæ
ãã®èšäºã§ã¯ã2ã€ã®åé¡ã«çŠç¹ãåœãŠãŸããããŸããSQLèšèªã®äžéšã®æ©èœïŒãã¹ããããã¯ãšãªãå€éšçµåãéèšããã¹ããããã³ã¬ã¯ã·ã§ã³ãšã®çµåïŒãã¢ããªã³ã°ã³ã³ããŒãã³ãã«è»¢éããŸãã次ã«ãã¢ããªã³ã°ã³ã³ããŒãã³ããžã®2ã€ã®æ°ããæ§é ã®å°å ¥ïŒæŠå¿µã®å¿åå®çŸ©ãšé¢æ°ãéããŠå®çŸ©ãããæŠå¿µã
å¿åã®æŠå¿µã¯ãæŠå¿µå®çŸ©ã®çç¥åœ¢ã§ããé¢æ°ã®åŒæ°ãšããŠïŒäœ¿çšããããã®ãã®ã§ èŠã€ãã findOneãããã㊠ååšããïŒåã¯ã«ããããã¹ããããæŠå¿µã®å®çŸ©ãšã㊠whereå¥..ãããã¯ãé¢æ°åããã°ã©ãã³ã°èšèªã®ç¡åé¢æ°å®çŸ©ã«é¡äŒŒããŠãããšèããããšãã§ããŸãã
é¢æ°ã«ãã£ãŠå®çŸ©ãããæŠå¿µã¯æŠå¿µã§ããããã®ãªããžã§ã¯ããçæããæ¹æ³ã¯ãã¢ã«ãŽãªãºã ã䜿çšããŠæ瀺çãªåœ¢åŒã§è¡šçŸãããŸããããã¯ãé¢æ°åãŸãã¯ãªããžã§ã¯ãæåããã°ã©ãã³ã°ãšè«çããã°ã©ãã³ã°ã®äžçã®éã®äžçš®ã®ãã€ã³ã¿ãŒãã§ãŒã¹ãã§ããæŠå¿µãå®çŸ©ããè«ççãªæ¹æ³ã䟿å©ãŸãã¯äžå¯èœãªå Žåãå€ãã®å Žåã«åœ¹ç«ã¡ãŸããããšãã°ãããŒã¿ããŒã¹ããã¡ã€ã«ããŸãã¯ãªã¢ãŒããµãŒãã¹ãžã®èŠæ±ããåæãã¡ã¯ããããŒããããŠãããŒãµã«è«çæ€çŽ¢ããã®ç¹å®ã®ãã®ã«çœ®ãæãããªã©ã§ããæé©åãããå®è£ ããŸãã¯ãªããžã§ã¯ããäœæããä»»æã®ã«ãŒã«ãå®è£ ããŸãã
èå³æ·±ãããšã«ããã¹ããããã¯ãšãªãå€éšçµåããã¹ããããã³ã¬ã¯ã·ã§ã³çµåãªã©ã®SQLããã®åçšã¯ãã¢ããªã³ã°ã³ã³ããŒãã³ãã®ããžãã¯ãå€§å¹ ã«å€æŽããå¿ èŠããªããå¿åã®æŠå¿µãé¢æ°ãä»ããæŠå¿µãªã©ã®æŠå¿µã䜿çšããŠå®è£ ãããŸãããããã¯ããããã®ã¿ã€ãã®æŠå¿µããåªããè¡šçŸåãåããæè»ã§çšéã®åºãããŒã«ã§ããããšã瀺åããŠããŸããé¢çœã䜿ãæ¹ã¯ãã£ãšãããããããšæããŸãã
ãã®ããããã®èšäºãšä»¥åã®2ã€ã®èšäºã§ã¯ããã€ããªããããã°ã©ãã³ã°èšèªã®ã¢ããªã³ã°ã³ã³ããŒãã³ãã®åºæ¬çãªæŠå¿µãšèŠçŽ ã«ã€ããŠèª¬æããŸãããããããã¢ããªã³ã°ã³ã³ããŒãã³ããé¢æ°åãŸãã¯ãªããžã§ã¯ãæåããã°ã©ãã³ã°ã¹ã¿ã€ã«ãå®è£ ããèšç®ã³ã³ããŒãã³ããšçµ±åããåé¡ã«é²ãåã«ã次ã®èšäºã§ãã®ã¢ããªã±ãŒã·ã§ã³ã®å¯èœãªãªãã·ã§ã³ã«å°å¿µããããšã«ããŸãããç§ã®èŠ³ç¹ããã¯ãã¢ããªã³ã°ã³ã³ããŒãã³ãã¯ãåŸæ¥ã®ã¯ãšãªèšèªïŒäž»ã«SQLïŒã«æ¯ã¹ãŠå©ç¹ããããèšç®ã³ã³ããŒãã³ããšæ·±ãçµ±åããããšãªãåç¬ã§äœ¿çšã§ããŸããããã«ã€ããŠã¯ã次ã®èšäºã§ããã€ãã®äŸã瀺ããŸãã
è±èªã®å®å šãªç§åŠçããã¹ãã¯ãpapers.ssrn.com / sol3 / papers.cfmïŒabstract_id = 3555711ã§å ¥æã§ããŸãã
以åã®åºçç©ãžã®ãªã³ã¯ïŒ
ãã«ããã©ãã€ã ããã°ã©ãã³ã°èšèªã®èšèšãããŒã1-ããã¯äœã®ããã§ããïŒ
ãã«ããã©ãã€ã ããã°ã©ãã³ã°èšèªãèšèšããŸããããŒã2-PL / SQLãLINQãGraphQLã§ã®æ§ç¯ã¢ãã«ã®æ¯èŒ
ãã«ããã©ãã€ã ããã°ã©ãã³ã°èšèª ãèšèšããŸããããŒã3-ç¥èè¡šçŸèšèªã®æŠèŠ
ãã«ããã©ãã€ã ããã°ã©ãã³ã°èšèª ãèšèšããŸããããŒã4-ã¢ããªã³ã°èšèªã®åºæ¬æ§é ãã«ããã©ãã€ã ããã°ã©ãã³ã°èšèªã
èšèšããŸããããŒã5-è«çããã°ã©ãã³ã°ã®æ©èœ