문제 설명
Oracle에서 쉼표로 구분된 문자열의 최대 개수를 얻는 방법은 무엇입니까? (How to get maximum COUNT of comma separated string in Oracle?)
테이블에 이와 같은 데이터를 저장하는 테이블이 있습니다.
MANUFACTURER_PART_NUMBER|MANUFACTURER_NAME |TEST_PLAN |ATTACHED_SUPPLIER_DOCUMENT |ATTACHED_LOGITECH_REPORT|
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑|
001059‑0000 |CHENGDA |268452,268453 |268456,268457,268459,268460|268465 |
001059‑0000 |SHANGHAI MARRISON CO.,LTD| |268458,268462 | |
001059‑0000 |SUZHOU SHARETECH | | | |
001059‑0000 |SYSTRON |268451,268452,268453|268456,268457,268459,268460|268465,268466 |
쉼표로 구분된 열의 최대 개수를 얻고 싶습니다. 예를 들어 TEST_PLAN에는 4번째 행에 최대 3개의 값이 있지만 ATTACHED_SUPPLIER_DOCUMENT에는 4개의 값이 있으므로 출력이 최대 개수로 4를 반환하도록 하고 싶습니다.
이 쿼리를 시도하여 이러한 열 값의 개수를 알아냈습니다.
WITH CTE AS(
SELECT DISTINCT
MP.PART_NUMBER MANUFACTURER_PART_NUMBER, M.NAME MANUFACTURER_NAME,
RTRIM(LTRIM(PG3.MULTILIST31, ','), ',') TEST_PLAN, regexp_count(MULTILIST31, ',') + 1 AS COL1,
RTRIM(LTRIM(PG3.MULTILIST32, ','), ',') ATTACHED_SUPPLIER_DOCUMENT, regexp_count(MULTILIST32, ',') + 1 AS COL2,
RTRIM(LTRIM(PG3.MULTILIST33, ','), ',') ATTACHED_LOGITECH_REPORT, regexp_count(MULTILIST33, ',') + 1 AS COL3
FROM MANU_PARTS MP
INNER JOIN MANUFACTURERS M ON M.ID = MP.MANU_ID
LEFT JOIN PAGE_TWO PG2 ON PG2.ID = MP.ID
LEFT JOIN PAGE_THREE PG3 ON PG3.ID = MP.ID
WHERE PART_NUMBER = '001059‑0000'
)
SELECT * FROM CTE
내가 찾고 있는 출력은 이렇습니다.
MANUFACTURER_PART_NUMBER|MANUFACTURER_NAME |TEST_PLAN |ATTACHED_SUPPLIER_DOCUMENT |ATTACHED_LOGITECH_REPORT|MAXCOUNT|
‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑|
001059‑0000 |SYSTRON |268451,268452,268453|268456,268457,268459,268460|268465,268466 | 4|
001059‑0000 |CHENGDA |268452,268453 |268456,268457,268459,268460|268465 | 4|
001059‑0000 |SHANGHAI MARRISON CO.,LTD| |268458,268462 | | 4|
001059‑0000 |SUZHOU SHARETECH | | | | 4|
참조 솔루션
방법 1:
Use REGEXP_COUNT to count commas. Use GREATEST to get the maximum count:
select
greatest(
regexp_count(test_plan, ','),
regexp_count(attached_supplier_document, ','),
regexp_count(attached_logitech_report, ',')
) + 1
from mytable;
If you want the maximum over all rows, use MAX:
select
max(
greatest(
regexp_count(test_plan, ','),
regexp_count(attached_supplier_document, ','),
regexp_count(attached_logitech_report, ',')
) + 1
)
from mytable;
IF you want the maximum count to be shown with each row, use MAX(...) OVER () instead of MAX(...).
(This gives a count of 1, if there is no value at all in a column. You can avoid this with a CASE expression, but I imagine that this is may not even be necessary, as there may always be a value in at least one of the columns.)
EDIT: (by Gordon)
You may need COALESCE() if any of the values are NULL:
select
greatest(
coalesce(regexp_count(test_plan, ','), 0),
coalesce(regexp_count(attached_supplier_document, ','), 0),
coalesce(regexp_count(attached_logitech_report, ','), 0)
) + 1
from mytable;
방법 2:
You don't need regular expressions and could instead, remove the commas and compare the length of the strings before and after replacement and add 1 to get the number of items. You can then use GREATEST to find the maximum over the columns and an analytic MAX function to find it over all the rows:
SELECT t.*,
MAX(
GREATEST(
COALESCE(
LENGTH( test_plan )
‑ LENGTH( REPLACE( test_plan, ',' ) )
+ 1,
0
),
COALESCE(
LENGTH( attached_supplier_document )
‑ LENGTH( REPLACE( attached_supplier_document, ',' ) )
+ 1,
0
),
COALESCE(
LENGTH( attached_logitech_report )
‑ LENGTH( REPLACE( attached_logitech_report, ',' ) )
+ 1,
0
)
)
) OVER ( PARTITION BY manufacturer_part_number )
AS maxcount
FROM table_name t
(Remove the PARTITION BY manufacturer_part_number so you have MAX( ... ) OVER () if you want to find the maximum over all rows rather than per each part number.)
So for your test data:
CREATE TABLE table_name (
MANUFACTURER_PART_NUMBER, MANUFACTURER_NAME, TEST_PLAN, ATTACHED_SUPPLIER_DOCUMENT, ATTACHED_LOGITECH_REPORT
) AS
SELECT '001059‑0000', 'CHENGDA', '268452,268453', '268456,268457,268459,268460','268465' FROM DUAL UNION ALL
SELECT '001059‑0000', 'SHANGHAI MARRISON CO.,LTD',NULL, '268458,268462', NULL FROM DUAL UNION ALL
SELECT '001059‑0000', 'SUZHOU SHARETECH', NULL, NULL, NULL FROM DUAL UNION ALL
SELECT '001059‑0000', 'SYSTRON', '268451,268452,268453','268456,268457,268459,268460','268465,268466' FROM DUAL;
This outputs:
MANUFACTURER_PART_NUMBER | MANUFACTURER_NAME | TEST_PLAN | ATTACHED_SUPPLIER_DOCUMENT | ATTACHED_LOGITECH_REPORT | MAXCOUNT :‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ | :‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ | :‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ | :‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ | :‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑ | ‑‑‑‑‑‑‑: 001059‑0000 | CHENGDA | 268452,268453 | 268456,268457,268459,268460 | 268465 | 4 001059‑0000 | SYSTRON | 268451,268452,268453 | 268456,268457,268459,268460 | 268465,268466 | 4 001059‑0000 | SUZHOU SHARETECH | null | null | null | 4 001059‑0000 | SHANGHAI MARRISON CO.,LTD | null | 268458,268462 | null | 4
db<>fiddle here
(by Muhammad Asim、Thorsten Kettner、MT0)