按2个字段组合分组，然后按每个组的总和排序，多个注释为django [英] Group by 2 fields combination and then order by the sum of each group, multiple annotations django

问题描述

我试图按发票的总保证金总和来订购顶级产品，如下所示:

I was trying to get top products ordered by their total margin sum from invoices as:

• 按给定的store_id
过滤发票
• 按product_name
分组
• 然后获取每个组的gross_margin的Sum
• 最后按gross_margin中的Sum的顺序排序(从高到低)

• Filter invoices by given store_id
• Group by product_name
• And the get the Sum of gross_margin of each group
• Finally order them by Sum of gross_margin (high to low)

以前的代码错误:

high_margin = StoreInvoiceBreakup.objects \
  .filter(store_invoice__store_id=store_id) \   
  .annotate(product_count=Count('product_name')) \
  .annotate(gross_margin_sum=Sum('gross_margin')) \
  .order_by('-gross_margin_sum') \
  .values(name=F('product_name')) \
  .distinct()[:int(top_count)]

然后通过

And then ended up solving multiple annotation problem via Stack Overflow similar question as:
(as previous code was giving wrong results.)

New correct code:

high_margin = StoreInvoiceBreakup.objects \
  .filter(store_invoice__store_id=store_id) \
  .values('product_name') \
  .annotate(gross_margin_sum=Sum('gross_margin')) \
  .order_by('gross_margin_sum') \
  .distinct()[:int(sell_range)]

输出看起来像:

"high_margin ": [{
  "product_name": "ABCD",
  "gross_margin_sum": 100  --sum of all margins for abcd
}...other products with sum]

哪个绝对是correct，但是遇到了另一个问题，例如:
商店的商品可以具有相同的product_name，但是有效期可能不同.
所以我想要的是，

Which is absolutely correct, but encountered another problem, as:
A store can have a product with same product_name, but may have different expiry date.
So what i want is,

• 按产品的product_name和expiry_date分组 组合.
• 然后获取每个组的margin总和，并按Sum的顺序返回，其中 不同的组合. (不仅限于不同的产品名称.)
  

• To group products by their product_name and expiry_date combination.
• Then get the margin sum for each group and return ordered by the Sum, with distinct combinations. (Not distinct product names only.)
  

*删除不同内容无济于事

或通过cursor进行 MySQL 查询也将有所帮助.如果无法通过 docs .
发票分类数据如下:

*Removing distinct does't help

Or a MySQL query via cursor to do so would also be helpful. If can't do it via queryset as by docs.
The invoice breakup data looks like:

  name  | margin  | ... | total | tax | etc..
  abcd  | 50      | ... |
  abcd  | 50      | ... |
  abcd  | 15      | ... |
  abcd  | 15      | ... |

输出应为:

"high_margin ": [{
  "product_name": "ABCD",
  "gross_margin_sum": 100  --sum for 2018 and abcd
  "expiry_date": 2018
},
{
  "product_name": "ABCD",
  "gross_margin_sum": 30  --sum for 2017 and abcd
  "expiry_date": 2017
},... other products with sum and expiry_date]

StoreProducts 如下:

  name  |  expiry_date  | size | price | etc...
  abcd  |  2018         |
  abcd  |  2017         |
  xyz   |  2019         |
  pqrs  |  2017         |

• 保证金仅存在于发票中，而不存在于商店产品中.
• 有效日期仅存在于商店产品中，不存在于发票中
• 我还希望能够更改最终输出名称.
• Invoice_product通过foreign_key映射到store_product_mapping， 然后将其映射到master_product.
• Margin only exist in invoice and not in store products.
• Expity date only exist in store products and not in invoice
• Also I want to be able to change final output names.
• Invoice_product is mapped via foreign_key to store_product_mapping, which then is mapped to master_product.

也许SQL查询看起来像:


Maybe SQL query look like:


SELECT NAME, EXPIRY_DATE, SUM(GROSS_MARGIN)
FROM INVOICE_BREAKUP GROUP BY NAME, EXPIRY_DATE
WHERE STORE_ID = 1

*不是实际流量

推荐答案

print(str(high_margin.query))

通过这种方式，您可以获取queryset生成的内容，并作为

This way you can get what a queryset produces and play around as its a lazy query as stated by answer here. So I got expected SQL query by just adding the expiry_date in the values, that produces group by name, expiry date as stated by docs.
Code changes as:

high_margin = StoreInvoiceBreakup.objects \
  .filter(store_invoice__store_id=store_id) \
  .values('product_name', 'expiry_date') \
  .annotate(gross_margin_sum=Sum('gross_margin')) \
  .order_by('gross_margin_sum') \
  .distinct()[:int(sell_range)]

*不确定是否正确

这篇关于按2个字段组合分组，然后按每个组的总和排序，多个注释为django的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！