在Fanout上产生准确的尺寸

发布于 2025-01-30 07:25:23 字数 617 浏览 4 评论 0原文

我的数据模型：

查询：

SELECT
    ProductSummary.Product,
    ProductSummary.ID AS SummaryID,
    Transactions.DateOfSale,
    Summary.Revenue
FROM
    ProductSummary JOIN
    Transactions ON (Transactions.ProductID = ProductSummary.ID)
WHERE
    Transactions.DateOfSale < '2014-01-10'

数据本身看起来不错，但是我也想显示一个小计，表的小计应该是当未连接该表时显示的数量。

例如，对于次序收入，答案应始终是我从中从摘要中获得的（收入）（应用于任何必要的过滤器之后）。如何生成？

原文

My data model:

The query:

SELECT
    ProductSummary.Product,
    ProductSummary.ID AS SummaryID,
    Transactions.DateOfSale,
    Summary.Revenue
FROM
    ProductSummary JOIN
    Transactions ON (Transactions.ProductID = ProductSummary.ID)
WHERE
    Transactions.DateOfSale < '2014-01-10'

The data itself looks fine, however I also want to show a subtotal, and the subtotal of a table should be the amount displayed when that table is not joined.

For example, for subtotaling Revenue the answer should always be what I would get from SELECT SUM(Revenue) FROM Summary (after applying any necessary filters). How to generate that?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

古镇旧梦 2025-02-06 07:25:23

做到这一点的一种方法是使用分析函数来计数唯一的行，例如：

WITH
    ProductSummary (Product, ID, Revenue) AS (
        SELECT 'Car', 1, 12 UNION ALL
        SELECT 'Phone', 2, 7
    ),
    Transactions (SummaryID, ID, DateOfSale) AS (
        SELECT 1, 1, DATE '2014-01-01' UNION ALL
        SELECT 1, 2, DATE '2014-01-02' UNION ALL
        SELECT 1, 3, DATE '2014-01-03' UNION ALL
        SELECT 2, 4, DATE '2014-01-04' UNION ALL
        SELECT 1, 5, DATE '2014-01-04' UNION ALL
        SELECT 1, 6, DATE '2014-01-04' UNION ALL
        SELECT 1, 7, DATE '2020-01-01'
    )
SELECT
    ProductSummary.Product,
    ProductSummary.ID AS SummaryID,
    Transactions.DateOfSale,
    ProductSummary.Revenue,
    IF(
        ROW_NUMBER() OVER (PARTITION BY ProductSummary.ID) = 1,
        ProductSummary.Revenue,
        0
    ) RevenueUnique
FROM
    ProductSummary 
    JOIN Transactions ON (Transactions.SummaryID=ProductSummary.ID)
WHERE
    Transactions.DateOfSale < DATE '2014-01-10';

One way to do this would be using an analytic function to count the unique rows while totaling, for example:

WITH
    ProductSummary (Product, ID, Revenue) AS (
        SELECT 'Car', 1, 12 UNION ALL
        SELECT 'Phone', 2, 7
    ),
    Transactions (SummaryID, ID, DateOfSale) AS (
        SELECT 1, 1, DATE '2014-01-01' UNION ALL
        SELECT 1, 2, DATE '2014-01-02' UNION ALL
        SELECT 1, 3, DATE '2014-01-03' UNION ALL
        SELECT 2, 4, DATE '2014-01-04' UNION ALL
        SELECT 1, 5, DATE '2014-01-04' UNION ALL
        SELECT 1, 6, DATE '2014-01-04' UNION ALL
        SELECT 1, 7, DATE '2020-01-01'
    )
SELECT
    ProductSummary.Product,
    ProductSummary.ID AS SummaryID,
    Transactions.DateOfSale,
    ProductSummary.Revenue,
    IF(
        ROW_NUMBER() OVER (PARTITION BY ProductSummary.ID) = 1,
        ProductSummary.Revenue,
        0
    ) RevenueUnique
FROM
    ProductSummary 
    JOIN Transactions ON (Transactions.SummaryID=ProductSummary.ID)
WHERE
    Transactions.DateOfSale < DATE '2014-01-10';