PostgreSQL: sum of values ​​connected via LATERAL join

Question

I am trying to clear data in a PostgreSQL table, where in some records there are a large number of profanities in a column email_address(the corresponding records were entered by agitated users as a result of disappointment due to an error that has since been fixed):

    ┌───────────────────┌
    │ email_address │
    ├───────────────────├
    │ [e-mail] │
    │ [e-mail]        │
    │ [e-mail]    │
    │ [e-mail]    │
    │ [e-mail]     │
    └───────────────────└

The output of the desired request

I would like to create a query that annotates each row from the data table with profanity assessment and orders account entries so that a person can go through the annotated data (presented in the web application) and take the necessary actions:

    ┌────────────────────────────┌
    │ email_address │ score │
    ├────────────────────────────├
    │ [e-mail]        │ 18 │
    │ [e-mail] │ 14 │
    │ [e-mail]    │ 9 │
    │ [e-mail]    │ 3 │
    │ [e-mail]     │ 0 │
    └────────────────────────────└

---

Attempt # 1

, , , ( 2 ...) , , email_address. profanities :

    ┌──────────────────┬───────┐
    │ profanity_regexp │ score │
    ├──────────────────┼───────┤
    │ foo              │     9 │
    │ bar(?!red)       │     5 │
    │ baz              │     3 │
    └──────────────────┴───────┘

, LATERAL regexp_matches, email_address ( ):

SELECT
    data.email_address,
    array_agg(matches)
FROM
    data,
    profanities p,
    LATERAL regexp_matches(data.email_address, p.posix_regexp, 'gi') matches
GROUP BY
    data.email_address;

:

    ┌───────────────────┬───────────────────┐
    │   email_address   │ profanities_found │
    ├───────────────────┼───────────────────┤
    │ [e-mail]       │ {{foo},{foo}}     │
    │ [e-mail]   │ {{foo}}           │
    │ [e-mail] │ {{foo},{bar}}     │
    │ [e-mail]   │ {{baz}}           │
    └───────────────────┴───────────────────┘

SUB-SELECT

, SQL:

SELECT
    data.email_address,
    array(
        SELECT score * ( 
            SELECT COUNT(*)
            FROM (SELECT
                regexp_matches(data.email_address, p.posix_regexp, 'gi')
            ) matches
        )
        FROM profanities p
    ) prof
from data;

( ) :

    ┌───────────────────┬──────────┐
    │   email_address   │   prof   │
    ├───────────────────┼──────────┤
    │ [e-mail] │ {9,5,0}  │
    │ [e-mail]       │ {18,0,0} │
    │ [e-mail]   │ {9,0,0}  │
    │ [e-mail]   │ {0,0,3}  │
    │ [e-mail]    │ {0,0,0}  │
    └───────────────────┴──────────┘

---

, ?

, ?

---

http://sqlfiddle.com/#!17/6685c/4

Answer 1

@daurnimator, , LATERAL . :

SELECT
    data.email_address,
    (
        SELECT
            coalesce(sum(s.score), 0) AS score
        FROM
            profanities,
            LATERAL (
                SELECT
                    profanities.score * array_length(
                        regexp_matches(
                            data.email_address,
                            profanities.profanity_regexp,
                            'gi'
                        ),
                        1
                    ) score
            ) s
    ) AS score
FROM
    data;

, , . , Django RawSQL , order_by('-score') .

Answer 2

. , .

SELECT email_address,
(
    SELECT SUM(s)
    FROM
        UNNEST(prof.profanity_score_subtotals) s
) AS sum_prof FROM (
    SELECT
        data.email_address,
        array(
            SELECT score * ( 
                SELECT COUNT(*)
                FROM (SELECT
                    regexp_matches(data.email_address, p.profanity_regexp, 'gi')
                ) matches
            )
            FROM profanities p
        ) profanity_score_subtotals
    FROM data
) prof;

Answer 3

- postgres set-return where, :

SELECT
    data.email_address,
    t.score
FROM
    data,
    LATERAL (
        SELECT
            coalesce(sum(s.score), 0) AS score
        FROM
            profanities,
            LATERAL (
                SELECT
                    profanities.score * array_length(
                        regexp_matches(
                            data.email_address,
                            profanities.profanity_regexp,
                            'gi'
                        ),
                        1
                    ) score
            ) s
    ) t;