
çŽtranslãïŒãã®èšäºã§ã¯ãè±åœã®äŒç€Ÿã§ããTicketsolveã®ããŒã ãªãŒããŒãã圌ã®éåžžã«å ·äœçãªåé¡ã®è§£æ±ºçãå ±æããææ°ã®MySQL8.0æ©èœã䜿çšããŠãããã环ç©é¢æ°ãäœæããããã®äžè¬çãªã¢ãããŒãã瀺ããŸãããã®ãªã¹ãã¯èŠèŠçã§ããã詳现ãªèª¬æãæäŸãããŠãããããããã»ã©æ·±ãæãäžããŠããªã人ã§ããåé¡ã®æ¬è³ªãçè§£ããã®ã«åœ¹ç«ã¡ãŸãã
MySQLã®çޝç©é¢æ°ã䜿çšããŠæŽæ°ãå®è¡ããããã®äžè¬çãªæŠç¥ã¯ãã«ã¹ã¿ã 倿°ãšãã¿ãŒã³ã䜿çšããããš
UPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar, mycol))
ã§ãã
ãã®ãã¿ãŒã³ã¯ãªããã£ãã€ã¶ãŒã§ã¯ããŸãæ©èœããªãããïŒé決å®çãªåäœã«ã€ãªããïŒã圌ãã¯ãããæŸæ£ããããšã«ããŸãããïŒæ¯èŒçïŒè€éãªããžãã¯ãå®è£ ããã®ãïŒå°ãªããšãåãåçŽãã§ïŒããå°é£ã«ãªããããçµæã¯äžçš®ã®ç©ºèã«ãªããŸãã
ãã®èšäºã§ã¯ããããå®è£ ãã2ã€ã®æ¹æ³ã«ã€ããŠèª¬æããŸãããŠã£ã³ããŠé¢æ°ã®äœ¿çšïŒæšæºçãªã¢ãããŒãïŒãšååž°çCTEã®äœ¿çšïŒäžè¬çãªããŒãã«åŒïŒã§ãã
èŠä»¶ãšèæ¯
CTEã¯ããªãçŽæçã§ãããCTEã«ããŸã詳ãããªã人ã¯ããã®ãããã¯ã«é¢ãã以åã®æçš¿ãåç §ããããšããå§ãããŸãã
ãŠã£ã³ããŠé¢æ°ã«ã€ããŠãåãããšãèšããŸããã¯ãšãª/æŠå¿µã«ã€ããŠè©³ããã³ã¡ã³ãããŸãããäžè¬çãªèãæ¹ã¯åé¡ãããŸããããŠã£ã³ããŠæ©èœã«ç¹åããæ¬ãåºçç©ã¯ãããããããŸãïŒãããç§ããŸã ãããã«ã€ããŠæžããŠããªãçç±ã§ãïŒããã ããã»ãšãã©ã®äŸã§ã¯ãèšç®ã¯è²¡åçµæãŸãã¯äººå£çµ±èšææšã®ããããã§å®è¡ãããŸãããã ãããã®èšäºã§ã¯å®éã®ã±ãŒã¹ã䜿çšããŸãã
ãœãããŠã§ã¢ã®å ŽåãMySQL 8.0.19ã®äœ¿çšããå§ãããŸãïŒå¿ é ã§ã¯ãããŸããïŒãåå©çšããã«ã¯ããã¹ãŠã®åŒãåãã³ã³ãœãŒã«ã§å®è¡ããå¿ èŠããããŸã
@venue_id
ã
ãœãããŠã§ã¢ã®äžçã§ã¯ãæåãªã¢ãŒããã¯ãã£äžã®ãžã¬ã³ãããããŸããããžãã¯ã¯ã¢ããªã±ãŒã·ã§ã³ã¬ãã«ã§å®è£ ããå¿ èŠããããŸããããããšãããŒã¿ããŒã¹ã¬ãã«ã§å®è£ ããå¿ èŠããããŸãããããã¯å®å šã«æå¹ãªè³ªåã§ãããç§ãã¡ã®å Žåãããžãã¯ã¯åºæ¬ã¬ãã«ã®ãŸãŸã§ããå¿ èŠããããšæ³å®ããŠããŸãããã®çç±ã¯ãããšãã°ãé床èŠä»¶ã§ããå¯èœæ§ããããŸãïŒç§ãã¡ã®å Žåã®ããã«ïŒã
ä»äº
ãã®ã¿ã¹ã¯ã§ã¯ãç¹å®ã®ããŒã«ïŒåå ŽïŒã«åº§åžãå²ãåœãŠãŸãã
ããžãã¹äžã®ç®çã§ãåå Žæã«ã¯ããããããã°ã«ãŒãåãïŒããã衚ã远å ã®çªå·ïŒãå²ãåœãŠãå¿ èŠããããŸãã
ã°ã«ãŒãåã®å€ã決å®ããããã®ã¢ã«ãŽãªãºã ã¯æ¬¡ã®ãšããã§ãã
- 0ããéå§ããå·Šäžã
- çŸåšã®è¡ãšåã®è¡ã®éã«ç©ºã®ã¹ããŒã¹ãããå ŽåããŸãã¯ãããæ°ããè¡ã§ããå Žåã¯ãåã®å€ã«2ã远å ããŸãïŒããã絶察çãªæåã®å Žæã§ãªãå ŽåïŒããã以å€ã®å Žåã¯ãå€ã1å¢ãããŸãã
- ã°ã«ãŒããå Žæã«å²ãåœãŠãŸãã
- åãè¡ã®æ°ããå ŽæãŸãã¯æ¬¡ã®è¡ïŒåã®è¡ãçµãã£ãŠããå ŽåïŒã«ç§»åãããã€ã³ã2ããç¹°ãè¿ããŸããå ŽæããªããªããŸã§ãã¹ãŠãç¶ããŸãã
ç䌌ã³ãŒãã®ã¢ã«ãŽãªãºã ïŒ
current_grouping = 0
for each row:
for each number:
if (is_there_a_space_after_last_seat or is_a_new_row) and is_not_the_first_seat:
current_grouping += 2
else
current_grouping += 1
seat.grouping = current_grouping
å®éã«ã¯ãå·ŠåŽã®æ§æã§å³åŽã«ç€ºãå€ãæå®ããå¿ èŠããããŸãïŒ
xâ 0 1 2 0 1 2
y âââââ¬ââââ¬ââââ® âââââ¬ââââ¬ââââ®
â 0 â x â x â â â 1 â 2 â â
âââââŒââââŒâââ†âââââŒââââŒââââ€
1 â x â â x â â 4 â â 6 â
âââââŒââââŒâââ†âââââŒââââŒââââ€
2 â x â â â â 8 â â â
â°ââââŽââââŽâââ⯠â°ââââŽââââŽââââ¯
ãã¬ãŒãã³ã°
ããŒã¹ããŒãã«ãæ¬¡ã®æå°éã®æ§é ã«ããŸãã
CREATE TABLE seats (
id INT AUTO_INCREMENT PRIMARY KEY,
venue_id INT,
y INT,
x INT,
`row` VARCHAR(16),
number INT,
`grouping` INT,
UNIQUE venue_id_y_x (venue_id, y, x)
);
ç§ãã¡ã¯æ¬åœã«å¿ èŠã¯ãããŸãã
row
ããåãnumber
ãäžæ¹ãã¬ã³ãŒããå®å
šã«ã€ã³ããã¯ã¹ã«å«ãŸããŠããããŒãã«ã¯äœ¿çšããããããŸããïŒå®éã®åââé¡ã«è¿ã¥ããããã ãã§ãïŒã
äžã®å³ã«åºã¥ããšãåå Žæã®åº§æšã¯ïŒyãxïŒã§ãã
- ïŒ0ã0ïŒãïŒ0ã1ïŒ
- ïŒ1ã0ïŒãïŒ1ã2ïŒ
- ïŒ20ïŒ
è¡ã®è¿œè·¡ã容æã«ãããããæåã®åº§æšãšããŠyã 䜿çšããŠããããšã«æ³šæããŠãã ããã
ãªããã£ãã€ã¶ãäºæããªãçããã¹ãèŠã€ããªãããã«ãååãªæ°ã®ã¬ã³ãŒããããŒãããå¿ èŠããããŸãããã¡ãããååž°CTEã䜿çšããŸãã
INSERT INTO seats(venue_id, y, x, `row`, number)
WITH RECURSIVE venue_ids (id) AS
(
SELECT 0
UNION ALL
SELECT id + 1 FROM venue_ids WHERE id + 1 < 100000
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */
v.id,
c.y, c.x,
CHAR(ORD('A') + FLOOR(RAND() * 3) USING ASCII) `row`,
FLOOR(RAND() * 3) `number`
FROM venue_ids v
JOIN (
VALUES
ROW(0, 0),
ROW(0, 1),
ROW(1, 0),
ROW(1, 2),
ROW(2, 0)
) c (y, x)
;
ANALYZE TABLE seats;
ããã€ãã®æ³šæïŒ
- ããã§ã¯ãCTEãè峿·±ãïŒããŸãããã°ïŒïŒæ¹æ³ã§äœ¿çšãããŠããŸããåã«ãŒãã¯venue_idã衚ããŸãããäŒå Žããšã«è€æ°ã®å Žæãçæããå¿ èŠããããããå Žæãå«ãããŒãã«ãšã®çžäºçµåãè¡ããŸãã
- v8.0.19ïŒ
VALUES ROW()...
ïŒè¡ã³ã³ã¹ãã©ã¯ã¿ãŒã¯ãïŒçµåå¯èœãªïŒããŒãã«ãå®éã«äœæããã«è¡šãããã«äœ¿çšãããŸãã - è¡ãšçªå·ã®ã©ã³ãã ãªå€ããã¬ãŒã¹ãã«ããŒãšããŠçæããŸãã
- ç°¡åã«ããããã«ãæé©åã¯è¡ããŸããã§ããïŒããšãã°ãããŒã¿ã¿ã€ããå¿ èŠä»¥äžã«åºããã¬ã³ãŒããæ¿å ¥ããåã«ã€ã³ããã¯ã¹ã远å ããããªã©ïŒã
å€ãã¢ãããŒã
å€ãè¯ãã¢ãããŒãã¯éåžžã«åçŽæå¿«ã§ãã
SET @venue_id = 5000; -- venue id; () id
SET @grouping = -1;
SET @y = -1;
SET @x = -1;
WITH seat_groupings (id, y, x, `grouping`, tmp_y, tmp_x) AS
(
SELECT
id, y, x,
@grouping := @grouping + 1 + (seats.x > @x + 1 OR seats.y != @y),
@y := seats.y,
@x := seats.x
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
)
UPDATE
seats s
JOIN seat_groupings sg USING (id)
SET s.grouping = sg.grouping
;
-- Query OK, 5 rows affected, 3 warnings (0,00 sec)
ããã¯ç°¡åã§ããïŒãã ããèŠåãå¿ããªãã§ãã ããïŒã
å°ããªéžè±ïŒãã®å Žåãç§ã¯ããŒã«æŒç®ã®ããããã£ã䜿çšããŸããæ¬¡ã®åŒã¯åçã§ãã
SELECT seats.x > @x + 1 OR seats.y != @y `increment`;
SELECT IF (
seats.x > @x + 1 OR seats.y != @y,
1,
0
) `increment`;
ãããçŽæçã«æãã人ãããã°ãããã§ãªã人ãããŸããããã¯å¥œã¿ã®åé¡ã§ããããããã¯ãã£ãšã³ã³ãã¯ããªè¡šçŸã䜿ããŸãã
çµæãèŠãŠã¿ãŸãããïŒ
SELECT id, y, x, `grouping` FROM seats WHERE venue_id = @venue_id ORDER BY y, x;
-- +-------+------+------+----------+
-- | id | y | x | grouping |
-- +-------+------+------+----------+
-- | 24887 | 0 | 0 | 1 |
-- | 27186 | 0 | 1 | 2 |
-- | 29485 | 1 | 0 | 4 |
-- | 31784 | 1 | 2 | 6 |
-- | 34083 | 2 | 0 | 8 |
-- +-------+------+------+----------+
çŽ æŽãããã¢ãããŒãïŒ
æ®å¿µãªãããããã«ã¯ããã€ããŒãªãæ¬ ç¹ããããŸããæ©èœããªãå Žåãé€ããŠãããŸãæ©èœããŸã...
ãã€ã³ãã¯ãã¯ãšãªãªããã£ãã€ã¶ãå¿ ãããå·Šããå³ã«èšç®ãå®è¡ãããšã¯éããªããããå²ãåœãŠïŒïŒ=ïŒãééã£ãé åºã§å®è¡ãããå¯èœæ§ãããããšã§ããééã£ãçµæã«ã€ãªãããŸããMySQLãæŽæ°ããåŸã人ã ã¯ãã°ãã°ãã®åé¡ã«çŽé¢ããŸãã
MySQL 8.0ã§ã¯ããã®æ©èœã¯å®éã«éæšå¥šã«ãªã£ãŠããŸãã
-- UPDATE.
--
SHOW WARNINGS\G
-- *************************** 1. row ***************************
-- Level: Warning
-- Code: 1287
-- Message: Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
-- [...]
ããŠãç¶æ³ãçŽããïŒ
çŸä»£ã®ã¢ãããŒãïŒ1ïŒãŠã£ã³ããŠæ©èœ
ãŠã£ã³ããŠé¢æ°ã®å°å ¥ã¯ãMySQLã®äžçã§éåžžã«æåŸ ãããŠããã€ãã³ãã§ãã
äžè¬çã«ããŠã£ã³ããŠé¢æ°ã®ãã¹ã©ã€ããã®æ§è³ªã¯ã环ç©é¢æ°ã§ããŸãæ©èœããŸãããã ããäžéšã®è€éãªçޝç©é¢æ°ã§ã¯ãæåŸã®åŒã®çµæãå¿ èŠã§ãããŠã£ã³ããŠé¢æ°ã¯åãæäœããããããããã®é¢æ°ã¯ãµããŒãããŠããŸããã
ããã¯ãåé¡ã解決ã§ããªãããšãæå³ããã®ã§ã¯ãªããåèããå¿ èŠãããã ãã§ãã
ç§ãã¡ã®å Žåãã¿ã¹ã¯ã¯2ã€ã®éšåã«åããããšãã§ããŸããåå Žæã®ã°ã«ãŒãåã¯ã次ã®2ã€ã®å€ã®åèšãšèŠãªãããšãã§ããŸãã
- åå Žæã®ã·ãªã¢ã«çªå·ã
- ããããåã®ãã¹ãŠã®å Žæã®å¢åã®çޝç©å€ã
ãŠã£ã³ããŠæ©èœã«ç²ŸéããŠãã人ã¯ãããã§å žåçãªãã¿ãŒã³ãèªèããŸãã
åã·ãŒãã®ã·ãŒã±ã³ã¹çªå·ã¯ãçµã¿èŸŒã¿ã®æ©èœã§ãã
ROW_NUMBER() OVER <window>
ãããã环ç©å€ã䜿çšãããšããã¹ãŠãã¯ããã«è峿·±ããã®ã«ãªããŸã...ãããèšç®ããã«ã¯ã次ã®2ã€ã®ã¢ã¯ã·ã§ã³ãå®è¡ããŸãã
- åå Žæã®å¢åãã«ãŠã³ããããããããŒãã«ïŒãŸãã¯CTEïŒã«æžãçããŸãã
- 次ã«ãå Žæããšã«ããŠã£ã³ããŠé¢æ°ã䜿çšããŠãã®å Žæã®å¢åãåèšããŸãã
SQLãèŠãŠã¿ãŸãããã
WITH
increments (id, increment) AS
(
SELECT
id,
x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x)
)
SELECT
s.id, y, x,
ROW_NUMBER() OVER tzw + SUM(increment) OVER tzw `grouping`
FROM seats s
JOIN increments i USING (id)
WINDOW tzw AS (ORDER BY y, x)
;
-- +-------+---+---+----------+
-- | id | y | x | grouping |
-- +-------+---+---+----------+
-- | 24887 | 0 | 0 | 1 |
-- | 27186 | 0 | 1 | 2 |
-- | 29485 | 1 | 0 | 4 |
-- | 31784 | 1 | 2 | 6 |
-- | 34083 | 2 | 1 | 8 |
-- +-------+---+---+----------+
ãããïŒ
ïŒç°¡åã«ããããã«ãä»åŸã¯UPDATEãçç¥ããŠããããšã«æ³šæããŠãã ãããïŒ
ãªã¯ãšã¹ããåæããŠã¿ãŸãããã
é«ã¬ãã«ã®ããžãã¯
次ã®CTE ïŒç·šéæžã¿ïŒïŒ
SELECT
id,
x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw `increment`
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x)
;
-- +-------+-----------+
-- | id | increment |
-- +-------+-----------+
-- | 24887 | 0 |
-- | 27186 | 0 |
-- | 29485 | 1 |
-- | 31784 | 1 |
-- | 34083 | 1 |
-- +-------+-----------+
âŠåã®å Žæããã®åå Žæã®å¢åãèšç®ããŸãïŒè©³çްã¯
LAG()
åŸã§èª¬æããŸãïŒãããã¯ããã¹ãŠã®ã¬ã³ãŒããšãã®åã®ã¬ã³ãŒãã§æ©èœãã环ç©çã§ã¯ãããŸããã
ããã§ã环ç©å¢åãèšç®ããããã«ããŠã£ã³ããŠé¢æ°ã䜿çšããŠãåå ŽæãŸã§ã®åèšãèšç®ããŸãã
-- (CTE here...)
SELECT
s.id, y, x,
ROW_NUMBER() OVER tzw `pos.`,
SUM(increment) OVER tzw `cum.incr.`
FROM seats s
JOIN increments i USING (id)
WINDOW tzw AS (ORDER BY y, x);
-- +-------+---+---+------+-----------+
-- | id | y | x | pos. | cum.incr. | (grouping)
-- +-------+---+---+------+-----------+
-- | 24887 | 0 | 0 | 1 | 0 | = 1 + 0 (curr.)
-- | 27186 | 0 | 1 | 2 | 0 | = 2 + 0 (#24887) + 0 (curr.)
-- | 29485 | 1 | 0 | 3 | 1 | = 3 + 0 (#24887) + 0 (#27186) + 1 (curr.)
-- | 31784 | 1 | 2 | 4 | 2 | = 4 + 0 (#24887) + 0 (#27186) + 1 (#29485) + 1 (curr.)
-- | 34083 | 2 | 1 | 5 | 3 | = 5 + 0 (#24887) + 0 (#27186) + 1 (#29485) + 1 (#31784)âµ
-- +-------+---+---+------+-----------+ + 1 (curr.)
LAGïŒïŒãŠã£ã³ããŠé¢æ°
LAG颿°ã¯ãæãåçŽãªåœ¢åŒïŒ
LAG(x)
ïŒã§ãæå®ãããåã®åã®å€ãè¿ããŸãããã®ãããªæ©èœã®å
žåçãªäžäŸ¿ã¯ããŠã£ã³ããŠã®æåã®ãšã³ããªãåŠçããããšã§ãã以åã®ã¬ã³ãŒãããªããããNULLãè¿ããŸããLAGã®å Žåã3çªç®ã®ãã©ã¡ãŒã¿ãŒãšããŠç®çã®å€ãæå®ã§ããŸãã
LAG(x, 1, x - 1) -- `x -1`
LAG(y, 1, y) -- `y`
ããã©ã«ãå€ãæå®ããããšã«ããããŠã£ã³ããŠã®å¢çã®æåã®å Žæããè¡ïŒyïŒã倿Žããã«ãä»ã®å ŽæïŒx-1ïŒã®æ¬¡ã®å Žæãšåãããžãã¯ãæã€ããã«ããŸãã
å¥ã®è§£æ±ºçã¯ã䜿çšããããš
IFNULL
ã§ãããåŒã¯éåžžã«é¢åã§ãã
-- , !
--
IFNULL(x > LAG(x) OVER tzw + 1 OR y != LAG(y) OVER tzw, 0)
IFNULL(x > LAG(x) OVER tzw + 1, FALSE) OR IFNULL(y != LAG(y) OVER tzw, FALSE)
2çªç®ã®ãã©ã¡ãŒã¿ãŒ
LAG()
ã¯ããŠã£ã³ããŠå
ã§æ»ãäœçœ®ã®æ°ã§ãã1ã¯åã®å€ã§ãïŒããã¯ããã©ã«ãã§ããããŸãïŒã
æè¡çåŽé¢
ååä»ããŠã£ã³ããŠ
ç§ãã¡ã®ã¯ãšãªã¯åããŠã£ã³ããŠãäœåºŠã䜿çšããŸããæ¬¡ã®2ã€ã®ã¯ãšãªã¯åœ¢åŒçã«åçã§ãã
SELECT
id,
x > LAG(x, 1, x - 1) OVER tzw + 1
OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x);
SELECT
id,
x > LAG(x, 1, x - 1) OVER (ORDER BY y, x) + 1
OR y != LAG(y, 1, y) OVER (ORDER BY y, x)
FROM seats
WHERE venue_id = @venue_id;
ãã ãã2ã€ç®ã¯ãæé©ã§ãªãåäœã«ã€ãªããå¯èœæ§ããããŸãïŒå°ãªããšãéå»ã«ééããŸããïŒããªããã£ãã€ã¶ãŒã¯ããŠã£ã³ããŠãç¬ç«ããŠãããšèŠãªãããããããåå¥ã«èšç®ã§ããŸãããã®ãããåžžã«ååä»ããŠã£ã³ããŠã䜿çšããããšããå§ãããŸãïŒå°ãªããšãç¹°ãè¿ãããå ŽåïŒã
PARTITIONBYã¹ããŒãã¡ã³ã
éåžžããŠã£ã³ããŠæ©èœã¯ããŒãã£ã·ã§ã³ã§å®è¡ãããŸããç§ãã¡ã®å Žåãæ¬¡ã®ããã«ãªããŸãã
SELECT
id,
x > LAG(x, 1, x - 1) OVER tzw + 1
OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (PARTITION BY venue_id ORDER BY y, x); -- !
ãŠã£ã³ããŠã¯ã¬ã³ãŒãã®å®å šãªã»ããïŒæ¡ä»¶ã«ãã£ãŠãã£ã«ã¿ãªã³ã°ããã
WHERE
ïŒãšäžèŽããããããŠã£ã³ããŠïŒããŒãã£ã·ã§ã³ïŒãæå®ããå¿
èŠã¯ãããŸããã
ãã ãããã®ã¯ãšãªãããŒãã«å šäœã§å®è¡
seats
ããå¿
èŠãããå Žåã¯ããã¹ãŠã®ãŠãŒã¶ãŒã®ãŠã£ã³ããŠããªã»ãããããããã«ããå¿
èŠããããŸãvenue_id
ã
äžŠã¹æ¿ã
ãªã¯ãšã¹ã
ORDER BY
ã¯ãŠã£ã³ããŠã¬ãã«ã§èšå®ãããŸãã
SELECT
id,
x > LAG(x, 1, x - 1) OVER tzw + 1
OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x)
ãã®å ŽåããŠã£ã³ããŠã®äžŠã¹æ¿ãã¯SELECTãšã¯å¥ã®ãã®ã§ããããã¯éåžžã«éèŠã§ãïŒãã®ãªã¯ãšã¹ãã®åäœïŒ
SELECT
id,
x > LAG(x, 1, x - 1) OVER tzw + 1
OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS ()
ORDER BY y, x
âŠæªå®çŸ©ãããã¥ã¢ã«ã«ç®ãåããŸãããïŒ
ã¯ãšãªçµæã®æååã¯ãWHEREãGROUP BYãããã³HAVINGå¥ãå®è¡ãããåŸã®FROMå¥ããæ±ºå®ããããŠã£ã³ããŠå ã§ã®å®è¡ã¯ORDER BYãLIMITãããã³SELECTDISTINCTã®åã«è¡ãããŸãã
ããã€ãã®èæ ®äºé
äžè¬çã«ããã®ã¿ã€ãã®åé¡ã§ã¯ãåã¬ã³ãŒããåã®ã¬ã³ãŒãã®é¢æ°ãšããŠè¡šãã®ã§ã¯ãªããåã¬ã³ãŒãã®ç¶æ å€åãèšç®ããŠããåèšããã®ãçã«ããªã£ãŠããŸãã
ãã®ãœãªã¥ãŒã·ã§ã³ã¯ã眮ãæããæ©èœãããè€éã§ãããåæã«ä¿¡é Œæ§ããããŸããæ®å¿µãªããããã®ã¢ãããŒãã¯åžžã«å¯èœãŸãã¯ç°¡åã«å®è£ ã§ãããšã¯éããŸãããããã§ãååž°CTEã圹ç«ã¡ãŸãã
ææ°ã®ã¢ãããŒãïŒ2ïŒååž°çCTE
MySQLã®CTEã®æ©èœã¯éãããŠããããããã®ã¢ãããŒãã«ã¯å°ã泚æãå¿ èŠã§ããäžæ¹ãããã¯äžèœã®çŽæ¥çãªãœãªã¥ãŒã·ã§ã³ã§ãããããã°ããŒãã«ãªã¢ãããŒããåèããå¿ èŠã¯ãããŸããã
æçµãªã¯ãšã¹ãã®ç°¡ç¥çããå§ããŸãããã
-- `p_` `Previous`
--
WITH RECURSIVE groupings (p_id, p_venue_id, p_y, p_x, p_grouping) AS
(
(
SELECT id, venue_id, y, x, 1
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
LIMIT 1
)
UNION ALL
SELECT
s.id, s.venue_id, s.y, s.x,
p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
FROM groupings, seats s
WHERE s.venue_id = p_venue_id AND (s.y, s.x) > (p_y, p_x)
ORDER BY s.venue_id, s.y, s.x
LIMIT 1
)
SELECT * FROM groupings;
ãã³ãŽïŒãã®ã¯ãšãªã¯ïŒæ¯èŒçïŒåçŽã§ãããããã«éèŠãªããšã«ã环ç©ã°ã«ãŒãå颿°ãå¯èœãªéãåçŽãªæ¹æ³ã§è¡šçŸããŸãã
p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
-- :
@grouping := @grouping + 1 + (seats.x > @x + 1 OR seats.y != @y),
@y := seats.y,
@x := seats.x
CTEã«ããŸã詳ãããªã人ã§ããè«çã¯æç¢ºã§ããæåã®åã¯ã次ã®é åºã§ããŒã«ã®æåã®åº§åžã§ãã
SELECT id, venue_id, y, x, 1
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
LIMIT 1
ååž°çãªéšåã§ã¯ã次ã®ããšãç¹°ãè¿ããŸãã
SELECT
s.id, s.venue_id, s.y, s.x,
p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
FROM groupings, seats s
WHERE s.venue_id = p_venue_id AND (s.y, s.x) > (p_y, p_x)
ORDER BY s.venue_id, s.y, s.x
LIMIT 1
æ¡ä»¶
WHERE
äžç·ã«ãªãã¬ãŒã¿ãšORDER BY
ããLIMIT
åã«æ¬¡ã«ãåããšå ŽæãèŠã€ããvenue_id
ãã䜿çšã®ããã®é
åïŒvenue_idãXãYïŒã«lshimi座æšïŒxãyïŒããœãŒãåŒ
ã®éšå
s.venue_id
ã¯éåžžã«éèŠã§ãïŒããã«ãããã€ã³ããã¯ã¹ã䜿çšã§ããŸãã
ãªãã¬ãŒã¿ãŒ
SELECT
ïŒ
- èç©ãå®è¡ïŒèšç®
(p_)grouping
ïŒã - çŸåšã®äœçœ®ïŒã®å€ãæäŸã
s.id
ãs.venue_id
ãs.y
ãs.x
次ã®ãµã€ã¯ã«ã§ïŒã
FROM groupings
CTEã®ååž°æ§ã®èŠä»¶ãæºããããš
ãéžæããŸãã
ããã§è峿·±ãã®ã¯ãååž°CTEãå埩åãšããŠäœ¿çš
groupings
ããååž°ãµãã¯ãšãªã§ããŒãã«ãããã§ãããããããçµåããŠseats
ãããã«åŠçããããã®ããŒã¿ãèŠã€ããããšã§ãã
JOIN
ã¯æ£åŒã«ã¯ã¯ãã¹ã§ãããæŒç®åã®ããã«LIMIT
1ã€ã®ã¬ã³ãŒãã®ã¿ãè¿ãããŸãã
äœæ¥ããŒãžã§ã³
æ®å¿µãªãããäžèšã®ã¯ãšãªã¯
ORDER BY
çŸåšååž°ãµãã¯ãšãªã§ãµããŒããããŠããªããããæ©èœããŸãããããã«ãLIMIT
ããã§äœ¿çšãããã»ãã³ãã£ã¯ã¹ã¯ãå€éšã¯ãšãªã«é©çšãããäžè¬çãªã»ãã³ãã£ã¯ã¹ãšã¯ç°ãªããŸãã
LIMITããµããŒããããããã«ãªããŸãã[..]çµæã®ããŒã¿ã»ãããžã®åœ±é¿ã¯ãå€éšSELECTã§LIMITã䜿çšããå Žåãšåãã§ãã
ããããããã¯ããã»ã©æ·±å»ãªåé¡ã§ã¯ãããŸãããåäœäžã®ããŒãžã§ã³ãèŠãŠã¿ãŸãããïŒ
WITH RECURSIVE groupings (p_id, p_venue_id, p_y, p_x, p_grouping) AS
(
(
SELECT id, venue_id, y, x, 1
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
LIMIT 1
)
UNION ALL
SELECT
s.id, s.venue_id, s.y, s.x,
p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
FROM groupings, seats s WHERE s.id = (
SELECT si.id
FROM seats si
WHERE si.venue_id = p_venue_id AND (si.y, si.x) > (p_y, p_x)
ORDER BY si.venue_id, si.y, si.x
LIMIT 1
)
)
SELECT * FROM groupings;
-- +-------+------+------+------------+
-- | p_id | p_y | p_x | p_grouping |
-- +-------+------+------+------------+
-- | 24887 | 0 | 0 | 1 |
-- | 27186 | 0 | 1 | 2 |
-- | 29485 | 1 | 0 | 4 |
-- | 31784 | 1 | 2 | 6 |
-- | 34083 | 2 | 0 | 8 |
-- +-------+------+------+------------+
ãµãã¯ãšãªã䜿çšããã®ã¯å°ãäžå¿«ã§ããããã®ã¢ãããŒãã¯æ©èœãããšã«ããããã€ãã®åŒãå¿ èŠãªãããããã§ã¯å®åæãæå°éã«æããããŸãã
ããã§ã¯ããŠããªã³
groupings
ãšã«é¢é£ä»ããããé åºä»ããšå¶éãè¡ã代ããã«seats
ããµãã¯ãšãªå
ã§ãããè¡ãããããå€éšã¯ãšãªã«æž¡ããŸããå€éšã¯ãšãªã¯ãã¿ãŒã²ããã¬ã³ãŒãã®ã¿ãéžæããŸãã
ããã©ãŒãã³ã¹ã«é¢ããèå¯
EXPLAINANALYZEã䜿çšããŠã¯ãšãªå®è¡èšç»ã調ã¹ãŠã¿ãŸãããã
mysql> EXPLAIN ANALYZE WITH RECURSIVE groupings [...]
-> Table scan on groupings (actual time=0.000..0.001 rows=5 loops=1)
-> Materialize recursive CTE groupings (actual time=0.140..0.141 rows=5 loops=1)
-> Limit: 1 row(s) (actual time=0.019..0.019 rows=1 loops=1)
-> Index lookup on seats using venue_id_y_x (venue_id=(@venue_id)) (cost=0.75 rows=5) (actual time=0.018..0.018 rows=1 loops=1)
-> Repeat until convergence
-> Nested loop inner join (cost=3.43 rows=2) (actual time=0.017..0.053 rows=2 loops=2)
-> Scan new records on groupings (cost=2.73 rows=2) (actual time=0.001..0.001 rows=2 loops=2)
-> Filter: (s.id = (select #5)) (cost=0.30 rows=1) (actual time=0.020..0.020 rows=1 loops=5)
-> Single-row index lookup on s using PRIMARY (id=(select #5)) (cost=0.30 rows=1) (actual time=0.014..0.014 rows=1 loops=5)
-> Select #5 (subquery in condition; dependent)
-> Limit: 1 row(s) (actual time=0.007..0.008 rows=1 loops=9)
-> Filter: ((si.y,si.x) > (groupings.p_y,groupings.p_x)) (cost=0.75 rows=5) (actual time=0.007..0.007 rows=1 loops=9)
-> Index lookup on si using venue_id_y_x (venue_id=groupings.p_venue_id) (cost=0.75 rows=5) (actual time=0.006..0.006 rows=4 loops=9)
èšç»ã¯æåŸ ã«æ²¿ã£ããã®ã§ãããã®å Žåãæé©ãªèšç»ã®åºç€ã¯ã€ã³ããã¯ã¹æ€çŽ¢ã«ãããŸãã
-> Nested loop inner join (cost=3.43 rows=2) (actual time=0.017..0.053 rows=2 loops=2)
-> Single-row index lookup on s using PRIMARY (id=(select #5)) (cost=0.30 rows=1) (actual time=0.014..0.014 rows=1 loops=5)
-> Index lookup on si using venue_id_y_x (venue_id=groupings.p_venue_id) (cost=0.75 rows=5) (actual time=0.006..0.006 rows=4 loops=9)
...æãéèŠãªããšãã€ã³ããã¯ã¹ã¹ãã£ã³ãå®è¡ãããšãããã©ãŒãã³ã¹ãå€§å¹ ã«äœäžããŸãïŒã€ãŸããå¿ èŠãªã¬ã³ãŒããäžåºŠã«æ¢ãã®ã§ã¯ãªããã€ã³ããã¯ã¹ã¬ã³ãŒããç·åœ¢ã«ã¹ãã£ã³ããŸãïŒã
ãã®ããã«ãä»äºãžã®ãã®æŠç¥ã®ããã«ãé¢é£ãã玢åŒã¯å Žæã«ãªããã°ãªããŸãããããªããã£ãã€ã¶ã«ãã£ãŠãå¯èœãªéãå¹ççã«äœ¿çšããããšã
å°æ¥çã«å¶éãè§£é€ãããå Žåããµãã¯ãšãªã䜿çšããå¿ èŠããªããªãããªããã£ãã€ã¶ã®ã¿ã¹ã¯ãå€§å¹ ã«ç°¡çŽ åãããŸãã
次åã®èšç»ã®ä»£æ¿æ¡
æé©ãªèšç»ã決å®ã§ããªãå Žåã¯ãäžæããŒãã«ã䜿çšããŠãã ããã
CREATE TEMPORARY TABLE selected_seats (
id INT NOT NULL PRIMARY KEY,
y INT,
x INT,
UNIQUE (y, x)
)
SELECT id, y, x
FROM seats WHERE venue_id = @venue_id;
WITH RECURSIVE
groupings (p_id, p_y, p_x, p_grouping) AS
(
(
SELECT id, y, x, 1
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
LIMIT 1
)
UNION ALL
SELECT
s.id, s.y, s.x,
p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
FROM groupings, seats s WHERE s.id = (
SELECT ss.id
FROM selected_seats ss
WHERE (ss.y, ss.x) > (p_y, p_x)
ORDER BY ss.y, ss.x
LIMIT 1
)
)
SELECT * FROM groupings;
ãã®ã¯ãšãªã§ã€ã³ããã¯ã¹ã¹ãã£ã³ãæž¡ããããšããŠããããŒãã«ã
selected_seats
éåžžã«å°ãããããå€ãã®è²»çšãããããŸãã
çµè«
å¹ççã§ãããæ¬ é¥ã®ããã¯ãŒã¯ãããŒããMySQL8.0ã§å°å ¥ãããéåžžã«åçŽãªæ©èœã«çœ®ãæããããããšãéåžžã«å¬ããæããŸãã
ãããŸã§ã®éã8.0ã®æ°æ©èœã®éçºã¯ç¶ç¶ãããŠããããã§ã«æåããŠãããªãªãŒã¹ãããã«æ¹åãããŠããŸãã
æåããååž°ïŒ
翻蚳è ããã®PS
ç§ãã¡ã®ããã°ãèªãã§ãã ããïŒ
- " MySQLïŒPercona ServerïŒã5.7ãã8.0ã«ã¢ããã°ã¬ãŒããã";
- "ããŒã¿ããŒã¹ãšKubernetesïŒã¬ãã¥ãŒãšãããªã¬ããŒãïŒ ";
- ãããå€ãã®éçºè ãããŒã¿ããŒã¹ã«ã€ããŠãããç¥ãå¿ èŠããããŸããã