Оффлайн
- Регистрация
- 1 Мар 2015
- Сообщения
- 1,481
- Баллы
- 155
Problem description & analysis:
The field of a certain database table is strings.
Task: Now we need to find the strings that contain at least 5 consecutive letters sorted alphabetically, or in other words, the longest substring in a continuous ascending order with a length greater than or equal to 5. Note to exclude non-letter characters.
Code comparisons:
SQL：
SELECT value
FROM table_name t
CROSS JOIN LATERAL (
SELECT MIN(lvl) AS start_pos
FROM (
SELECT LEVEL AS lvl,
SUBSTR(t.value, LEVEL, 1) AS ch
FROM DUAL
CONNECT BY LEVEL <= LENGTH(t.value)
)
MATCH_RECOGNIZE(
ORDER BY lvl
MEASURES
FIRST(lvl) AS lvl
PATTERN (first_row consecutive{3,})
DEFINE first_row AS (ch BETWEEN 'A' AND 'Z' OR ch BETWEEN 'a' AND 'z'),
consecutive AS (ch BETWEEN 'A' AND 'Z' OR ch BETWEEN 'a' AND 'z')
AND ASCII(PREV(ch)) + 1 = ASCII(ch)
)
HAVING MIN(lvl) > 0
)
SQL needs to use nested subqueries and CONNECT BY to simulate sequences, and then use MATCH_RECOGNIZE to process sequences. The code is cumbersome and difficult to understand.
SPL: SPL directly provides sequence calculation functions:
??
A1： Load data.
A2： Split the string into a sequence by character, group the sequence, and start a new group when the current member is less than or equal to the previous member or the previous member is not a letter. Select the string with the longest group that has a length greater than or equal to 5. The function group can keep the grouped subsets for subsequent processing after grouping, rather than immediately aggregating them. @i represents grouping according to conditions.
Free to Try, Powerful to Use — .
The field of a certain database table is strings.
Task: Now we need to find the strings that contain at least 5 consecutive letters sorted alphabetically, or in other words, the longest substring in a continuous ascending order with a length greater than or equal to 5. Note to exclude non-letter characters.
Code comparisons:
SQL：
SELECT value
FROM table_name t
CROSS JOIN LATERAL (
SELECT MIN(lvl) AS start_pos
FROM (
SELECT LEVEL AS lvl,
SUBSTR(t.value, LEVEL, 1) AS ch
FROM DUAL
CONNECT BY LEVEL <= LENGTH(t.value)
)
MATCH_RECOGNIZE(
ORDER BY lvl
MEASURES
FIRST(lvl) AS lvl
PATTERN (first_row consecutive{3,})
DEFINE first_row AS (ch BETWEEN 'A' AND 'Z' OR ch BETWEEN 'a' AND 'z'),
consecutive AS (ch BETWEEN 'A' AND 'Z' OR ch BETWEEN 'a' AND 'z')
AND ASCII(PREV(ch)) + 1 = ASCII(ch)
)
HAVING MIN(lvl) > 0
)
SQL needs to use nested subqueries and CONNECT BY to simulate sequences, and then use MATCH_RECOGNIZE to process sequences. The code is cumbersome and difficult to understand.
SPL: SPL directly provides sequence calculation functions:
??
A1： Load data.
A2： Split the string into a sequence by character, group the sequence, and start a new group when the current member is less than or equal to the previous member or the previous member is not a letter. Select the string with the longest group that has a length greater than or equal to 5. The function group can keep the grouped subsets for subsequent processing after grouping, rather than immediately aggregating them. @i represents grouping according to conditions.
Free to Try, Powerful to Use — .