Как группировать и агрегировать поля Int и array[int]?

Итак, допустим, у меня есть таблица с двумя полями: first_col — это int, а second_col — это array[int]:

from sqlalchemy import (
    Table,
    select,
    Integer,
)
from sqlalchemy.dialects.postgresql import ARRAY



some_table = Table(
    "some_table",
    Column("id", Integer, primary_key=True),
    Column("first_col", Integer),
    Column("second_col", ARRAY(Integer)),
)

И я хочу взять все объекты из db как с first_col, так и с second_col и, например, объединить их, я могу показать то, что хочу, в коде Python:

def get_some_stuff_and_aggregate() -> dict:
    query = (
        select(
            [
                some_table.c.first_col,
                some_table.c.second_col,
            ]
        )
        .select_from(some_table)
    )
    # execute query here... it's not important here
    query_res = ...

    res_dict = {}
    for some_obj in query_res:
        for some_ids in (
            [some_obj.first_col],
            some_obj.second_col,
        ):
            for some_id in some_ids:
                if some_id in res_dict:
                    res_dict[some_id] += 1
                else:
                    res_dict[some_id] = 1

    return res_dict

Но, думаю, я смогу сделать это на уровне SQL (ORM) без этой грязной и медленной агрегации Python. Не могли бы вы мне помочь? Как это сделать лучше, используя ORM?

Итак, вам нужно подсчитать, сколько раз каждое значение появляется в первом или втором столбце?

— 19.06.2024 16:29

Да, именно то, что вы говорите, верно

— 19.06.2024 16:32

python postgresql sqlalchemy

19.06.2024 16:20

Почему в Python есть оператор "pass"?

Оператор pass в Python - это простая концепция, которую могут быстро освоить даже новички без опыта программирования.

Некоторые методы, о которых вы не знали, что они существуют в Python

Python - самый известный и самый простой в изучении язык в наши дни. Имея широкий спектр применения в области машинного обучения, Data Science,...

Основы Python Часть I

Вы когда-нибудь задумывались, почему в программах на Python вы видите приведенный ниже код?

LeetCode - 1579. Удаление максимального числа ребер для сохранения полной проходимости графа

Алиса и Боб имеют неориентированный граф из n узлов и трех типов ребер:

Оптимизация кода с помощью тернарного оператора Python

И последнее, что мы хотели бы показать вам, прежде чем двигаться дальше, это

Советы по эффективной веб-разработке с помощью Python

Как веб-разработчик, Python может стать мощным инструментом для создания эффективных и масштабируемых веб-приложений.

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Вероятно, существует лучший SQL, но, похоже, он делает то, о чем вы просите.

from sqlalchemy import Column, Integer, MetaData, Table, create_engine, func, insert, select
from sqlalchemy.dialects.postgresql import ARRAY, array
from sqlalchemy.orm import Session

engine = create_engine("postgresql+psycopg://")
metadata = MetaData()
some_table = Table(
    "some_table",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("first_col", Integer),
    Column("second_col", ARRAY(Integer)),
)
metadata.create_all(engine)

dummy_data = [
    {"first_col": 1, "second_col": [1, 2, 1, 4, 5]},
    {"first_col": 3, "second_col": [2, 4, 6, 2, 10]},
    {"first_col": 6, "second_col": [3, 6, 9, 3, 15]},
    {"first_col": 4, "second_col": [4, 8, 4, 16, 20]},
    {"first_col": 2, "second_col": [5, 10, 15, 5, 25]},
]

with Session(engine) as session:
    session.execute(insert(some_table).values(dummy_data))
    session.commit()

with Session(engine) as session:
    subq = select(
        func.unnest(
            some_table.c.second_col.op("||")(array([some_table.c.first_col]))
        ).label("element")
    ).subquery()

    statement = select(subq.c.element, func.count("*")).group_by(subq.c.element)

    for i in session.execute(statement):
        print(i)

Это генерирует следующий sql и выходные данные.

SELECT
    anon_1.element,
    count(% (count_2)
        s::VARCHAR) AS count_1
FROM (
    SELECT
        unnest(some_table.second_col || ARRAY [some_table.first_col]) AS element
    FROM
        some_table) AS anon_1
GROUP BY
    anon_1.element

(9, 1)
(15, 2)
(3, 3)
(5, 3)
(4, 5)
(10, 2)
(6, 3)
(2, 4)
(16, 1)
(25, 1)
(20, 1)
(1, 3)
(8, 1)

19.06.2024 19:15

Другие вопросы по теме

Множественный случай и условия

Операции сохранения не фиксируются при использовании аннотации @Scheduled

Почему порядок меняется при использовании UNION ALL в PostgreSQL?

Различный совокупный счетчик в оконной функции PostgreSQL

Повторяющееся значение ключа нарушает ограничение уникальности при попытке вставки

Почему я получаю синтаксическую ошибку при использовании выбора?

Обновление Spring Boot JPA с использованием составных первичных ключей

Используйте Hibernate для проверки соответствия перечисления Java перечислению Postgres

Postgres не использует индекс, когда отфильтрованное/индексированное значение не существует

Эффективная стратегия одновременного опроса рабочих процессов в PostgreSQL без дубликатов

Как группировать и агрегировать поля Int и array[int]?

Ответы 1

Другие вопросы по теме

Похожие вопросы