Какую парадигму SQL использовать при группировании подсчетов по типу?

#sql #django #postgresql #count #django-orm

#sql #django #postgresql #количество #django-orm

Вопрос:

Допустим, у меня есть база данных с данными из пиццерии в ней. Я отслеживаю клиентов и их заказы с помощью двух таблиц: customer и orders . orders имеет значение FK для customer , чтобы я мог легко видеть, какие заказы какому клиенту принадлежат.

Я могу подсчитать все заказы клиента следующим образом:

 SELECT
    c.id,
    COUNT(o.id) AS order_counts
FROM
    customers AS c,
    JOIN orders ON c.id = o.customer_id
GROUP BY
    c.id

Что дает мне что-то вроде этого:

 results = [
{
  “customer_number”: 1,
  “order_counts”: 5
},
{
  “customer_number”: 2,
  “order_counts”: 10
}]

Однако, что, если я хочу «разнести» результаты order_counts, чтобы показать мне все отдельные типы пиццы, а затем посчитать эти типы? Я бы добавил новую таблицу с именем, pizzas которая имеет name столбец, а затем получил бы мои результаты, которые выглядели бы следующим образом:

 results = [
{
  “customer_number”: 1,
  “counts”: {
    “Hawaiian”: 2,
    “Meat Lovers”: 2,
    "Four Cheese": 1
  }
},
{
  “customer_number”: 2,
  “counts”: {
    “Hawaiian”: 5,
    “Meat Lovers”: 5,
    "Four Cheese": 0
  }
}]

Какие принципы / парадигмы SQL мне нужно использовать для достижения этого? Я подозреваю, что мне нужен подзапрос и / или вложенный оператор GROUP BY.

Дополнительный вопрос: возможно ли это в Django ORM, или это что-то, что довольно быстро преодолевает ограничения ORM?

1. Бонусный ответ: Да, с помощью DRF вы могли бы легко достичь этого. Вам просто нужно изменить сериализатор для этого внешнего ключа, по умолчанию он будет передавать только идентификатор связанного объекта.

Ответ №1:

Если вам действительно нужен такой вложенный вывод, то вы также можете сгенерировать JSON непосредственно из базы данных:

 select
    c.id customer_id,
    sum(no_pizzas) no_orders,
    jsonb_object_agg(p.name, o.cnt_pizza) counts
from customers AS c,
inner join (
    select customer_id, pizza_id, count(*) cnt_pizza
    from orders o 
    group by customer_id, pizza_id 
) o on c.id = o.customer_id
inner join pizza p  on p.id = o.pizza_id
group by c.id

Это создает столбец counts в виде объекта json с именами пиццы в качестве ключей и количеством пиццы в качестве значения. В качестве бонуса вы по-прежнему получаете общее количество заказов в столбце no_orders .

Если вы хотите учитывать клиентов, у которых нет заказов, используйте left join s:

 select
    c.id customer_id,
    coalesce(sum(no_pizzas), 0) no_orders,
    jsonb_object_agg(p.name, o.cnt_pizza) counts
from customers AS c,
left join (
    select customer_id, pizza_id, count(*) cnt_pizza
    from orders o 
    group by customer_id, pizza_id 
) o on c.id = o.customer_id
left join pizza p  on p.id = o.pizza_id
group by c.id

Ответ №2:

Результирующий результат может содержать несколько идентификаторов клиентов, но цифры специфичны для пиццы.

 SELECT
    c.id,
    COUNT(o.id) AS order_counts,
    p.name
FROM
    customers AS c,
    JOIN orders o ON c.id = o.customer_id
    JOIN pizzas p ON p.id = o.pizzas_id
GROUP BY
    c.id,
    p.name