[B! sql] naoty_kã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

naoty_k id:naoty_k

sqlã«é–¢ã™ã‚‹naoty_kã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (7)

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

${{author_name}}$
{{author_name}}{{created}}
{{ #comment }}{{ comment }}{{ /comment }}
- {{ label }}

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

ãªãœã€SQLã¯é‡ãŸããªã‚‹ã®ã‹ï¼Ÿâ”€â”€ã€ŽSQLãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹è©³è§£ã€ã®ç¿»è¨³è€…ãŒæ•™ãˆã‚‹åŽŸå› ã¨å¯¾ç–ï½œãƒã‚¤ã‚¯ãƒ©ã‚¹è»¢è·ãƒ»æ±‚äººæƒ…å ±ã‚µã‚¤ãƒˆ AMBIï¼ˆã‚¢ãƒ³ãƒ“ï¼‰
ãªãœã€SQLã¯é‡ãŸããªã‚‹ã®ã‹ï¼Ÿâ”€â”€ã€ŽSQLãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹è©³è§£ã€ã®ç¿»è¨³è€…ãŒæ•™ãˆã‚‹åŽŸå› ã¨å¯¾ç– ã€ŽSQLãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹è©³è§£ã€ã®ç¿»è¨³è€…ã®æ¾æµ¦éš¼äººã•ã‚“ã«ã€8ã¤ã®ã€ŒSQLãŒé‡ãŸããªã‚‹åŽŸå› ã¨ãã®å¯¾ç–ã€ã‚’èžãã¾ã—ãŸã€‚ã‚·ã‚¹ãƒ†ãƒ ã®ãƒœãƒˆãƒ«ãƒãƒƒã‚¯ã«ãªã‚‹ã‚ˆã†ãªã€Œå•é¡Œã®ã‚ã‚‹SQLã€ã‚’å›žé¿ã™ã‚‹ãƒŽã‚¦ãƒã‚¦ã‚’å¦ã³ã¾ã—ã‚‡ã†ã€‚ ãƒ‡ãƒ¼ã‚¿ã®æ“ä½œã‚„å®šç¾©ã‚’ã™ã‚‹è¨€èªžã€ŒSQLã€ã¯ã€ã©ã®ã‚ˆã†ãªé ˜åŸŸã‚’æ‹…ã†ã‚¨ãƒ³ã‚¸ãƒ‹ã‚¢ã«ã¨ã£ã¦ã‚‚å¿…ä¿®ç§‘ç›®ã§ã™ã€‚ã—ã‹ã—ã€ãã®ä»•æ§˜ã‚’ãã¡ã‚“ã¨ç†è§£ã—ã€ãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹ã«å„ªã‚ŒãŸSQLã‚’æ›¸ã‘ã‚‹æ–¹ã¯ãã‚Œã»ã©å¤šãã‚ã‚Šã¾ã›ã‚“ã€‚å•é¡Œã®ã‚ã‚‹SQLã‚’æ›¸ã„ã¦ã—ã¾ã„ã€çŸ¥ã‚‰ã¬é–“ã«ãã‚ŒãŒã‚·ã‚¹ãƒ†ãƒ ã®ãƒœãƒˆãƒ«ãƒãƒƒã‚¯ã«ãªã£ã¦ã—ã¾ã†äº‹æ…‹ã¯ã‚ˆãç™ºç”Ÿã—ã¾ã™ã€‚ ã§ã¯ã€ã©ã†ã™ã‚Œã°ãã†ã—ãŸäº‹æ…‹ã‚’å›žé¿ã§ãã‚‹ã®ã§ã—ã‚‡ã†ã‹ï¼Ÿ ãã®ãƒŽã‚¦ãƒã‚¦ã‚’å¦ã¶ãŸã‚ã€ä»Šå›žã¯ã€ŽSQLãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹è©³è§£ã€ã®ç¿»è¨³è€…ã§ã‚ã‚Šã€è‡ªèº«ã‚‚ã‚¨ãƒ³ã‚¸ãƒ‹ã‚¢ã§ã‚‚ã‚ã‚‹æ¾æµ¦éš¼äººï¼ˆã¾ã¤ã†ã‚‰ãƒ»ã¯ã‚„ã¨ï¼@dblmktï¼‰ã•ã‚“ã«8ã¤
naoty_k 2017/12/14
sql

performance
ãƒªãƒ³ã‚¯
ã‚µãƒ¼ãƒ“ã‚¹çµ‚äº†ã®ãŠçŸ¥ã‚‰ã›
ã‚µãƒ¼ãƒ“ã‚¹çµ‚äº†ã®ãŠçŸ¥ã‚‰ã› ã„ã¤ã‚‚Yahoo! JAPANã®ã‚µãƒ¼ãƒ“ã‚¹ã‚’ã”åˆ©ç”¨ã„ãŸã ãèª ã«ã‚ã‚ŠãŒã¨ã†ã”ã–ã„ã¾ã™ã€‚ ãŠå®¢æ§˜ãŒã‚¢ã‚¯ã‚»ã‚¹ã•ã‚ŒãŸã‚µãƒ¼ãƒ“ã‚¹ã¯æœ¬æ—¥ã¾ã§ã«ã‚µãƒ¼ãƒ“ã‚¹ã‚’çµ‚äº†ã„ãŸã—ã¾ã—ãŸã€‚ ä»Šå¾Œã¨ã‚‚Yahoo! JAPANã®ã‚µãƒ¼ãƒ“ã‚¹ã‚’ã”æ„›é¡§ãã ã•ã„ã¾ã™ã‚ˆã†ã€ã‚ˆã‚ã—ããŠé¡˜ã„ã„ãŸã—ã¾ã™ã€‚
naoty_k 2017/08/22
sql

performance
ãƒªãƒ³ã‚¯
http://labs.cybozu.co.jp/blog/kazuho/archives/2008/06/friends_timeline.php
naoty_k 2017/03/02
sql

mysql
ãƒªãƒ³ã‚¯
[SQL] ç§»å‹•å¹³å‡ã‚’ç®—å‡º ã€œWindowé–¢æ•°ã‚’ä½¿ã£ã¦å¤‰å‹•å‚¾å‘ã‚’æŽ´ã‚€ã€œ | DevelopersIO
ã¯ã˜ã‚ã« ç§»å‹•å¹³å‡ã®ç›®çš„ã¯ é•·æœŸé–“ã«ã‚ãŸã‚‹åŸºæœ¬çš„ãªå‚¾å‘å¤‰å‹•ã‚’è¦‹ã‚‹ã®ã«ç”¨ã„ã‚‰ã‚Œã‚‹ ã¨ã„ã†ã“ã¨ã ãã†ã§ã™ã€‚ å£ç¯€å¤‰å‹•ã‚„ä¸è¦å‰‡å¤‰å‹•ã‚’é™¤åŽ»ã—ãŸã‚Šã€åŠ é‡ç§»å‹•å¹³å‡ã¨ã„ã†ç›´è¿‘ã®ãƒ‡ãƒ¼ã‚¿ã«é‡ã¿ã‚’ã‚‚ãŸã›ã‚‹æ–¹æ³•ãŒã‚ã£ãŸã‚Šã™ã‚‹ã‚ˆã†ã§ã™ãŒã€ã“ã®ã‚ãŸã‚Šã¯ã‚ˆãã‚ã‹ã£ã¦ã„ãªã„ã®ã§è€ƒãˆãªã„ã“ã¨ã«ã—ã¾ã™ã€‚ ä»Šå›žã‚„ã£ã¦ã¿ã‚‹ã®ã¯å˜ç´”ç§»å‹•å¹³å‡ã¨ã„ã†ã‚‚ã®ã§ã™ã€‚ æ ªä¾¡ã®ç§»å‹•å¹³å‡ç·šã¿ãŸã„ãªã®ã‚‚ã“ã‚Œã§ã§ããã†ã§ã™ãï¼ã€€ã“ã‚“ãªã«å˜ç´”ã§ã¯ãªã„ã¨ã¯æ€ã„ã¾ã™ãŒã€‚ã€‚ ä»Šå›žã‚‚ã„ã¤ã‚‚ã®ã‚µãƒ³ãƒ—ãƒ«ã®ä¸ã‹ã‚‰paymentãƒ†ãƒ¼ãƒ–ãƒ«ã‚’ä½¿ã„ã¾ã™ã€‚ æ‰‹ã‚’å‹•ã‹ã—ãªãŒã‚‰SQLã®å‹•ä½œã‚’ç¢ºèªã—ãŸã„æ–¹ã¯ã“ã¡ã‚‰ã‚’ã©ã†ãžï¼ ç’°å¢ƒ PostgreSQL 9.5.4 ä»Šå›žã®SQL ä»Šå›žã®SQLã¯å½“æ—¥ã‚’å«ã‚€ç›´è¿‘ï¼”å–¶æ¥æ—¥ã®å£²ã‚Šä¸Šã’ã®ç§»å‹•å¹³å‡ã‚’ç®—å‡ºã™ã‚‹SQLã«ãªã‚Šã¾ã™ã€‚ å…ƒãƒ‡ãƒ¼ã‚¿ payment_id | customer_id | staff_id | rental_id | amoun
naoty_k 2017/01/26
sql
ãƒªãƒ³ã‚¯
ãƒˆãƒ¬ã‚¸ãƒ£ãƒ¼ãƒ‡ãƒ¼ã‚¿ã§å®Ÿè·µï¼šWindowé–¢æ•°ï¼ˆãã®ï¼“ï¼‰ - ãƒˆãƒ¬ã‚¸ãƒ£ãƒ¼ãƒ‡ãƒ¼ã‚¿ï¼ˆTreasure Dataï¼‰ãƒ–ãƒã‚°
ãƒˆãƒ¬ã‚¸ãƒ£ãƒ¼ãƒ‡ãƒ¼ã‚¿ã¯ã‚¯ãƒ©ã‚¦ãƒ‰ã§ãƒ‡ãƒ¼ã‚¿ãƒžãƒãƒ¼ã‚¸ãƒ¡ãƒ³ãƒˆã‚µãƒ¼ãƒ“ã‚¹ã‚’æä¾›ã—ã¦ã„ã¾ã™ã€‚ åŸºæœ¬å½¢3ï¼šAGGR_FUNC() OVER (PARTITION BY c ORDER BY d ROWS BETWEEN m PRECEDING AND n FOLLOWING) åŸºæœ¬å½¢3 ã¯PARTITION BY ã§ã‚°ãƒ«ãƒ¼ãƒ—åŒ–ã•ã‚Œï¼ŒORDER BY ã§é †åºã¥ã‘ã‚‰ã‚ŒãŸé›†åˆã‚’ã•ã‚‰ã«çµžã‚Šè¾¼ã‚€ãŸã‚ã«ä½¿ç”¨ã—ã¾ã™ã€‚Window é–¢æ•°ã«ãŠã‘ã‚‹çµžã‚Šè¾¼ã¿ã¯æ™®é€šã®SQLã§è¨˜è¿°ã•ã‚Œã‚‹WHEREå¥ã‚„LIMITå¥ã¨ã¯ç•°ãªã‚‹ç¨®ã®ã‚‚ã®ã§ã‚ã‚‹äº‹ã®èªè˜ã¯é‡è¦ã§ã™ã€‚ Window é–¢æ•°ã§ã¯ï¼ŒROWS BETWEEN ã§çµžã‚Šè¾¼ã‚€æ¡ä»¶ã¨ã—ã¦è‡ªåˆ†ã®ãƒ¬ã‚³ãƒ¼ãƒ‰ã®å‰å¾Œé–¢ä¿‚ã‚’æŒ‡å®šã™ã‚‹äº‹ãŒå¤§ããªç‰¹å¾´ã§ã™ã€‚ä¾‹ã‚’è¦‹ã¦ã¿ã¾ã—ã‚‡ã†ã€‚ ç§»å‹•å¹³å‡ è‡ªåˆ†ã®éŽåŽ» m å›žåˆ†ã®ãƒ¬ã‚³ãƒ¼ãƒ‰ã‚’é¡ã£ã¦æ±‚ã‚ã‚‰ã‚Œã‚‹å¹³å‡ã‚’ç§»å‹•å¹³å‡ã¨å‘¼ã³ã¾ã™ã€‚ ä¸‹ã®æ–‡ã§ã¯è‡ªèº«ã®å€¤ï¼‹éŽåŽ»ã®è‡ªå·±3ãƒ¬ã‚³ãƒ¼ãƒ‰å‰ã¾ã§ã®ç¯„
naoty_k 2017/01/26
treasure data

sql
ãƒªãƒ³ã‚¯
ç‰©ç†ã‚µãƒ¼ãƒã‚’é¸å®šã™ã‚‹éš›ã®ãƒã‚¤ãƒ³ãƒˆ â€“ Eureka Engineering â€“ Medium
Eureka EngineeringLearn about Eurekaâ€™s engineering efforts, product developments and more.
naoty_k 2017/01/25
sql
ãƒªãƒ³ã‚¯
åˆ†æžSQLã®ã‚³ãƒ¼ãƒ‡ã‚£ãƒ³ã‚°ã‚¹ã‚¿ã‚¤ãƒ« - ã‚¯ãƒƒã‚¯ãƒ‘ãƒƒãƒ‰é–‹ç™ºè€…ãƒ–ãƒã‚°
SQLã€æ›¸ã„ã¦ã¾ã™ã‹ï¼Ÿ ã“ã¨å¤§è¦æ¨¡ãƒ‡ãƒ¼ã‚¿å‡¦ç†ã®åˆ†é‡Žã«ãŠã„ã¦ã¯SQLã¯ã‚‚ã¯ã‚„æ¨™æº–ã‚¤ãƒ³ã‚¿ãƒ¼ãƒ•ã‚§ã‚¤ã‚¹ã§ã‚ã‚Šã€ åˆ†æžã‚„ã‚‰ãƒãƒƒãƒã‚„ã‚‰ã«é–¢ã‚ã£ã¦ã„ã‚‹çš†æ§˜ã¯æ—¥ã€…å¤§é‡ã®SQLã‚¯ã‚¨ãƒªãƒ¼ã‚’ç”Ÿç”£ã—ã¦ã„ã‚‹ã“ã¨ã¨æ€ã„ã¾ã™ã€‚ ãã“ã§ã¡ã‚‡ã£ã¨æ°—ã«ãªã‚‹ã®ãŒã€ SQLã®ã‚³ãƒ¼ãƒ‡ã‚£ãƒ³ã‚°ã‚¹ã‚¿ã‚¤ãƒ«ã£ã¦ã©ã†ã™ã‚‹ã®ãŒä¸€èˆ¬çš„ãªã‚“ã ã£ã‘â€¦â€¦ï¼Ÿ ã¨ã„ã†ç‚¹ã§ã™ã€‚ ã‚¤ãƒžãƒ‰ã‚ã¯SQLãªã‚“ã¦O/R mapperã«åã‹ã›ã‚‹ã“ã¨ãŒå¤šã„ã‹ã‚‰ãªã®ã‹ã€ ãã‚Œã¨ã‚‚ã‚³ãƒ¼ãƒ‰ã‚’åºƒã„ç¯„å›²ã§å…±æœ‰ã™ã‚‹ã“ã¨ãŒãã‚‚ãã‚‚ãªã„ã‹ã‚‰ã‹ã€ SQLã®ã‚³ãƒ¼ãƒ‡ã‚£ãƒ³ã‚°ã‚¹ã‚¿ã‚¤ãƒ«ã«ã¤ã„ã¦è¦‹èžãã™ã‚‹ã“ã¨ã¯ä»–ã®ãƒ—ãƒã‚°ãƒ©ãƒŸãƒ³ã‚°è¨€èªžã«æ¯”ã¹ã‚‹ã¨ã ã„ã¶å°‘ãªãã€ ã„ã¾ã„ã¡æ±ºå®šç‰ˆã¨è¨€ãˆã‚‹ã‚¹ã‚¿ã‚¤ãƒ«ãŒãªã„ãªã¨æ„Ÿã˜ã¦ã„ã¾ã™ã€‚ ãã‚“ãªã‚ã‘ã§æœ¬æ—¥ã¯ã€SQLã®ã‚³ãƒ¼ãƒ‡ã‚£ãƒ³ã‚°ã‚¹ã‚¿ã‚¤ãƒ«ã«ã¤ã„ã¦ã®æ„è˜ã‚’æ´»ç™ºåŒ–ã•ã›ã‚‹ã¹ãã€ ã‚¯ãƒƒã‚¯ãƒ‘ãƒƒãƒ‰ã§ã‚ãŸã—ï¼ˆé’æœ¨ï¼‰ãŒä½¿ã£ã¦ã„ã‚‹ã‚³ãƒ¼ãƒ‡ã‚£ãƒ³ã‚°ã‚¹ã‚¿ã‚¤ãƒ«ã‹ã‚‰ç‰¹å¾´çš„ãªç‚¹ã‚’ç´¹ä»‹ã—ãŸã„ã¨æ€ã„ã¾ã™ã€‚ ç‰¹ã«ã€åˆ†æž
naoty_k 2016/11/30
sql
ãƒªãƒ³ã‚¯
1