同行分析，國家，聚合

SELECT 
    shippingDestination,
    SUM(CAST(SUBSTRING_INDEX(transactionQuantity, ' ', 1) AS UNSIGNED)) AS totalQuantity
FROM (
    SELECT 
        JSON_UNQUOTE(JSON_EXTRACT(product_transaction_details, CONCAT('$[', numbers.n, '].shippingDestination'))) AS shippingDestination,
        JSON_UNQUOTE(JSON_EXTRACT(product_transaction_details, CONCAT('$[', numbers.n, '].transactionQuantity'))) AS transactionQuantity
    FROM 
        `biz_industry_new_pk_product_transactions`,
        (SELECT a.N + b.N * 10 AS n
         FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a,
              (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
         ORDER BY n
        ) numbers
    WHERE 
        pk_id='f8d52581d9fa430da62478ec87231f31' and create_time >'2024-12-26' and product_id in (select DISTINCT(product_id) from biz_industry_new_pk_product where pk_id='f8d52581d9fa430da62478ec87231f31' and type = '2')
        AND JSON_EXTRACT(product_transaction_details, CONCAT('$[', numbers.n, ']')) IS NOT NULL
) AS derived_table
GROUP BY 
    shippingDestination;