Nano Hash - криптовалюты, майнинг, программирование

LEFT Присоединиться к PSQL, где параметр AND находится в другой таблице

У меня есть запрос PSQL, в котором параметр AND слева присоединяется к другому примеру таблицы проверки таблицы ниже.

Счета

 id  | account |      invoice_date       | reference | total_amount | status 
-----+---------+-------------------------+-----------+--------------+--------
 164 |     100 | 2016-08-03 03:05:08.996 |       161 |      2000.00 |       
 165 |     100 | 2016-08-03 21:42:07.865 |       164 |            0 |       
 167 |     100 | 2016-08-03 22:56:41.731 |       166 |       100.00 |       
 168 |     100 | 1970-01-01 00:33:20     |       161 |          200 |       
 169 |     100 | 2016-08-08 00:00:00     |       161 |          200 |

Invoice_items

 id  | invoice | invoice_item_type | product | quantity | unit_price | reference | amount  
-----+---------+-------------------+---------+----------+------------+-----------+---------
 143 |     164 |                 1 |       6 |          |            |       161 | 2000.00
 144 |     165 |                 1 |      11 |          |            |       164 |       0
 145 |     167 |                 1 |       8 |          |            |       166 |  100.00

Есть еще одна таблица PRODUCTS, но единственное релевантное поле - это id.

это мой запрос

select products.id, sum(invoice_items.amount) as total_revenue
from products
    left join invoice_items on invoice_items.product = products.id
    left join invoices on invoice_items.invoice = invoices.id
                      and invoices.invoice_date= current_date
group by products.id;

Мне нужен запрос, чтобы перечислить все идентификаторы продуктов и в столбце total_revenue указать сумму продаж продукта (добавить «количество» в таблицу invoice_items, где поле «продукт» аналогично) за текущий день (найдено на Счета-фактуры). Но когда я запускаю этот запрос, он перечисляет все total_amounts для продуктов. Что мне не хватает?

Пример вывода. 8, 6 и 11 должны быть пустыми

id  | total_revenue 
-----+---------------
 125 |              
 154 |              
 119 |              
 129 |              
   8 |        100.00
 112 |              
   5 |              
 132 |              
 104 |              
 113 |              
 143 |              
 152 |              
 121 |              
 127 |              
 165 |              
 139 |              
 146 |              
  15 |              
   2 |              
 147 |              
 149 |              
 166 |              
 169 |              
  13 |              
 106 |              
 122 |              
   9 |              
  11 |             0
 110 |              
 120 |              
 130 |              
 155 |              
 134 |              
 136 |              
 101 |              
 168 |              
 131 |              
 157 |              
 161 |              
 103 |              
 150 |              
 159 |              
 107 |              
 108 |              
 145 |              
   4 |              
  12 |              
 158 |              
 167 |              
 138 |              
 162 |              
 100 |              
 156 |              
 163 |              
 124 |              
 123 |              
 109 |              
 153 |              
 102 |              
 105 |              
 151 |              
 116 |              
 133 |              
 140 |              
 160 |              
 148 |              
 126 |              
 141 |              
   7 |              
 118 |              
  10 |              
 164 |              
 128 |              
  14 |              
 144 |              
 135 |              
   1 |              
   6 |       2000.00
   3 |              
 137 |              
 117 |              
 142 |              
 111 |
postgresql sql left-join
09.08.2016

  • Что вы подразумеваете под 8, 6 и 11 должны быть пустыми. Поскольку у вас есть and для даты в left join, он вернет продукты, которые не совпадают. Глядя на ваш запрос, даже внутреннего соединения должно быть достаточно, если вам нужны доходы только для продуктов, для которых есть счет-фактура. 09.08.2016

Ответы:


1

Ограничение даты только отфильтровывает записи в таблице invoice, тогда как вам нужно, чтобы оно также отфильтровывало записи в таблице invoice_items, но оно не делает этого, потому что оба являются левыми соединениями. Производная таблица легко решит эту проблему и даст желаемый результат. Я также добавил несколько псевдонимов таблиц для краткости и удобочитаемости.

Нравится:

SELECT 
    p.id, SUM(inv.amount) AS total_revenue

FROM 
    products p  LEFT JOIN 

    (SELECT 
        ii.product, i.invoice_date, ii.amount 
     FROM 
        invoice_items ii JOIN
        invoices i ON 
            ii.invoice = i.id) inv ON 
            inv.product = p.id AND
            inv.invoice_date= current_date

GROUP BY p.id;
09.08.2016
  • это сработало, просто измените ii.invoice_date в производной таблице на i.invoice_date. Спасибо :* 09.08.2016

    • 2

    Вы получаете большое количество значений NULL, что кажется ожидаемым в зависимости от вашего запроса.

    Похоже, что это проблема "ВНУТРЕННЕГО" соединения, а не "левого" соединения. Левое соединение сохранит все экземпляры, в которых нет счетов-фактур для продукта в результирующем наборе.

    Вы также можете переместить весь запрос о «DATE» в предложение Have (лично я предпочитаю подзапросы в WHERE / Have, поскольку считаю логику более понятной):

    SELECT products.id, SUM(invoice_items.amount) AS total_revenue
FROM products
    INNER JOIN invoice_items ON invoice_items.product = products.id
HAVING EXISTS (SELECT 1 FROM invoices WHERE invoice_items.invoice = invoices.id
                      AND invoices.invoice_date= current_date)
GROUP BY products.id;
    09.08.2016

    3

    Понятно. Было бы полезно использовать меньшие выборочные данные с желаемыми результатами.

    Проблема немного сложна для объяснения. left join хранит все продукты и счета, независимо от даты. Последнее присоединение к invoices приносит счета-фактуры, соответствующие только на текущую дату. Однако, поскольку вы суммируете вторую таблицу, каждая совпадающая строка появляется один раз (даже если в счете за день нет позиций) и появляется в ваших результатах.

    Решение: используйте оператор case, чтобы определить, есть ли совпадение в таблице invoice элементов:

    select p.id,
       sum(ii.amount * (case when i.id is not null then 1 end)) as total_revenue
from products p left join
     invoice_items ii
     on ii.product = p.id left join
     invoices i
     on ii.invoice = i.id and i.invoice_date = current_date
group by p.id;

    Я также подозреваю, что правильное условие для свидания:

         on ii.invoice = i.id and i.invoice_date >= current_date and
        i.invoice_date < current_date + interval '1 day'

    Кроме того, написание этого как подзапроса сохраняет внешнюю агрегацию и также должно решить проблему:

    select p.*,
       (select sum(ii.amount)
        from invoice_items ii join
             invoices i
             on ii.invoice = i.id and i.invoice_date >= current_date and
                i.invoice_date < current_date + interval '1 day'
        where ii.product = p.id
       ) as total_revenue
from products p;
    09.08.2016
  • Я ожидал бы, что производительность приведенного выше примера с подзапросом в основном выборе будет очень низкой, если в таблицах окажется больше, чем тривиальное количество записей. Производная таблица, вероятно, будет работать лучше, поскольку ее нужно вычислять только один раз, а не один раз для каждого продукта. 09.08.2016
  • @SimonWoolf. . . При правильной индексации подзапрос может быть самым быстрым подходом. 09.08.2016
  • Как так? Такое же индексирование также улучшит производительность производной таблицы, которая, по моему опыту, всегда будет превосходить этот подход. 09.08.2016
    • Новые материалы
    Кластеризация: более глубокий взгляд
    Кластеризация — это метод обучения без учителя, в котором мы пытаемся найти группы в наборе данных на основе некоторых известных или неизвестных свойств, которые могут существовать. Независимо от..
    Как написать эффективное резюме
    Предложения по дизайну и макету, чтобы представить себя профессионально Вам не позвонили на собеседование после того, как вы несколько раз подали заявку на работу своей мечты? У вас может..
    Частный метод Python: улучшение инкапсуляции и безопасности
    Введение Python — универсальный и мощный язык программирования, известный своей простотой и удобством использования. Одной из ключевых особенностей, отличающих Python от других языков, является..
    Как я автоматизирую тестирование с помощью Jest
    Шутка для победы, когда дело касается автоматизации тестирования Одной очень важной частью разработки программного обеспечения является автоматизация тестирования, поскольку она создает..
    Работа с векторными символическими архитектурами, часть 4 (искусственный интеллект)
    Hyperseed: неконтролируемое обучение с векторными символическими архитектурами (arXiv) Автор: Евгений Осипов , Сачин Кахавала , Диланта Хапутантри , Тимал Кемпития , Дасвин Де Сильва ,..
    Понимание расстояния Вассерштейна: мощная метрика в машинном обучении
    В обширной области машинного обучения часто возникает необходимость сравнивать и измерять различия между распределениями вероятностей. Традиционные метрики расстояния, такие как евклидово..
    Обеспечение масштабируемости LLM: облачный анализ с помощью AWS Fargate и Copilot
    В динамичной области искусственного интеллекта все большее распространение получают модели больших языков (LLM). Они жизненно важны для различных приложений, таких как интеллектуальные..