sql - Grails - 通过对域中的多个元素进行分组和汇总来检索数据

Question

为标题道歉，我不确定如何准确地说出来。

给定这个域:

class PurchaseLog{

    Date dateCreated
    Date logDate // Specific day of log; not necessarily the day it was created
    Branch branch

    static hasMany = [itemCount: ItemCount]
}

和:

class ItemCount {

    Item item
    Long count

    static belongsTo = [purchaseLog : PurchaseLog]
}

系统应该按天或按周获取购买日志(购买了每件商品的数量)。给定来自每个分支的事务，它会为每天创建日志。

每天获取库存日志很容易，但是，系统应该合并一周内的项目计数以创建每周日志。我一直在尝试使用 SQL，加入 ItemCount带有 PurchaseLog 的表在对表格进行分组并合并每个项目的项目计数之前(获取每个项目每天的计数总和)。

这是我一直在尝试使用的 SQL 查询:

WITH purchase_log_list AS (SELECT pl.log_date, ic.item_id, ic.count, pl.branch_id FROM purchase_log AS pl LEFT JOIN item_count AS ic ON pl.id = ic.purchase_log_id ) 
SELECT item_id, SUM(count) AS countPerWeek, extract(YEAR FROM log_date) as year_group, extract(WEEK FROM log_date) as week_group FROM purchase_log_list WHERE branch_id = :branch_id GROUP BY year_group, week_group, item_id ORDER BY year_group, week_group

它仍然没有按预期工作，我已经为此工作了几天。对此有什么建议吗？

Answer 1

您可以使用 HQL为了那个原因。 HQL有几个与日期/时间相关的函数，如 day , dayofweek month , year等。您可以将这些功能与 group by 一起使用，以实现您想要的。例如，如果想要关于 PurchaLog.logDate 按周分组的项目总和。

    //Assuming Item has a name property   
    def logs = ItemCount.executeQuery("select item.name, purchaseLog.logDate, sum(count) from ItemCount group by item.name, dayofweek(purchaseLog.logDate)")

注1:这将返回列表列表，而不是 ItemCount 列表.

注2:每当您使用 aggregate在您选择的列中的函数，您必须在查询中明确提及您想要的字段名称，并且字段类型不能是其他域。就像如果你想获得 Item来自 ItemCount您需要提及 Item 的字段名称喜欢 select item.name ... .你不能只提到 select item ... .

这是 HQL Doc 的链接和 Grails executeQuery .但是我找不到 dayofweek在 HQL 文档中。但它受 HQL 支持，我已经使用它。这是一篇提到此 can we group by day of the week using hibernate? 的 SO 帖子