SQL查询按月比较产品销量

发布于 2024-07-04 08:03:39 字数 1862 浏览 10 评论 0原文

我有一个每月状态数据库视图，我需要根据该视图构建报告。视图中的数据如下所示：

Category | Revenue  |  Yearh  |  Month
Bikes      10 000      2008        1
Bikes      12 000      2008        2
Bikes      12 000      2008        3
Bikes      15 000      2008        1
Bikes      11 000      2007        2
Bikes      11 500      2007        3
Bikes      15 400      2007        4

...等等

该视图包含产品类别、收入、年份和月份。我想创建一份比较 2007 年和 2008 年的报告，显示没有销售的月份为 0。所以报告应该看起来像这样：

Category  |  Month  |  Rev. This Year  |  Rev. Last Year
Bikes          1          10 000               0
Bikes          2          12 000               11 000
Bikes          3          12 000               11 500
Bikes          4          0                    15 400

需要注意的关键是第 1 个月仅在 2008 年有销售额，因此 2007 年为 0。此外，第 4 个月仅在 2008 年没有销售额，因此为 0，而它在 2007 年有销售额并且仍然显示。

另外，该报告实际上是针对财政年度的 - 因此，如果 2007 年或 2008 年的第 5 个月没有销售，我希望在两个列中都包含 0 的空列。

我得到的查询看起来像这样

SELECT 
    SP1.Program,
    SP1.Year,
    SP1.Month,
    SP1.TotalRevenue,
    IsNull(SP2.TotalRevenue, 0) AS LastYearTotalRevenue

FROM PVMonthlyStatusReport AS SP1 
     LEFT OUTER JOIN PVMonthlyStatusReport AS SP2 ON 
                SP1.Program = SP2.Program AND 
                SP2.Year = SP1.Year - 1 AND 
                SP1.Month = SP2.Month
WHERE 
    SP1.Program = 'Bikes' AND
    SP1.Category = @Category AND 
    (SP1.Year >= @FinancialYear AND SP1.Year <= @FinancialYear + 1) AND
    ((SP1.Year = @FinancialYear AND SP1.Month > 6) OR 
     (SP1.Year = @FinancialYear + 1 AND SP1.Month <= 6))

ORDER BY SP1.Year, SP1.Month

：查询的一个问题是它不会返回上面示例数据中的第四行，因为我们在 2008 年没有任何销售，但我们实际上在 2007 年做了。

这可能是一个常见的查询/问题，但我的 SQL 执行后就生锈了前端开发这么久了。任何帮助是极大的赞赏！

哦，顺便说一句，我正在使用 SQL 2005 进行此查询，因此如果有任何有用的新功能可能对我有帮助，请告诉我。

原文

I have a Monthly Status database view I need to build a report based on. The data in the view looks something like this:

Category | Revenue  |  Yearh  |  Month
Bikes      10 000      2008        1
Bikes      12 000      2008        2
Bikes      12 000      2008        3
Bikes      15 000      2008        1
Bikes      11 000      2007        2
Bikes      11 500      2007        3
Bikes      15 400      2007        4

... And so forth

The view has a product category, a revenue, a year and a month. I want to create a report comparing 2007 and 2008, showing 0 for the months with no sales. So the report should look something like this:

Category  |  Month  |  Rev. This Year  |  Rev. Last Year
Bikes          1          10 000               0
Bikes          2          12 000               11 000
Bikes          3          12 000               11 500
Bikes          4          0                    15 400

The key thing to notice is how month 1 only has sales in 2008, and therefore is 0 for 2007. Also, month 4 only has no sales in 2008, hence the 0, while it has sales in 2007 and still show up.

Also, the report is actually for financial year - so I would love to have empty columns with 0 in both if there was no sales in say month 5 for either 2007 or 2008.

The query I got looks something like this:

SELECT 
    SP1.Program,
    SP1.Year,
    SP1.Month,
    SP1.TotalRevenue,
    IsNull(SP2.TotalRevenue, 0) AS LastYearTotalRevenue

FROM PVMonthlyStatusReport AS SP1 
     LEFT OUTER JOIN PVMonthlyStatusReport AS SP2 ON 
                SP1.Program = SP2.Program AND 
                SP2.Year = SP1.Year - 1 AND 
                SP1.Month = SP2.Month
WHERE 
    SP1.Program = 'Bikes' AND
    SP1.Category = @Category AND 
    (SP1.Year >= @FinancialYear AND SP1.Year <= @FinancialYear + 1) AND
    ((SP1.Year = @FinancialYear AND SP1.Month > 6) OR 
     (SP1.Year = @FinancialYear + 1 AND SP1.Month <= 6))

ORDER BY SP1.Year, SP1.Month

The problem with this query is that it would not return the fourth row in my example data above, since we didn't have any sales in 2008, but we actually did in 2007.

This is probably a common query/problem, but my SQL is rusty after doing front-end development for so long. Any help is greatly appreciated!

Oh, btw, I'm using SQL 2005 for this query so if there are any helpful new features that might help me let me know.

分享到QQ

分享到微博

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

发布评论

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

爱要勇敢去追 2024-07-11 08:03:39

Case 语句是我最好的 sql 朋友。您还需要一个表格，列出在这两个月中产生 0 转速的时间。

假设基于下表的可用性：

销售：类别 | 收入| 年|
月份

和

tm：年份| 月（填充了所有
报告所需日期）

没有空行的示例 1：

select
    Category
    ,month
    ,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year
    ,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year

from
    sales

where
    year in (2008,2007)

group by
    Category
    ,month

Category  |  Month  |  Rev. This Year  |  Rev. Last Year
Bikes          1          10 000               0
Bikes          2          12 000               11 000
Bikes          3          12 000               11 500
Bikes          4          0                    15 400

带有空行的示例 2：
我将使用子查询（但其他查询可能不会），并将为每个产品和年月组合返回一个空行。

select
    fill.Category
    ,fill.month
    ,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year
    ,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year

from
    sales
    Right join (select distinct  --try out left, right and cross joins to test results.
                   product
                   ,year
                   ,month
               from
                  sales --this ideally would be from a products table
                  cross join tm
               where
                    year in (2008,2007)) fill


where
    fill.year in (2008,2007)

group by
    fill.Category
    ,fill.month

Category  |  Month  |  Rev. This Year  |  Rev. Last Year
Bikes          1          10 000               0
Bikes          2          12 000               11 000
Bikes          3          12 000               11 500
Bikes          4          0                    15 400
Bikes          5          0                    0
Bikes          6          0                    0
Bikes          7          0                    0
Bikes          8          0                    0

请注意，大多数报告工具都会执行此交叉表或矩阵功能，现在我认为 SQL Server 2005 具有枢轴语法，也可以执行此操作。

这里有一些额外的资源。
案件
https://web.archive .org/web/20210728081626/https://www.4guysfromrolla.com/webtech/102704-1.shtml
SQL Server 2005 数据透视
http://msdn.microsoft.com/en-us/library/ms177410.aspx

The Case Statement is my best sql friend. You also need a table for time to generate your 0 rev in both months.

Assumptions are based on the availability of following tables:

sales: Category | Revenue | Yearh |
Month

and

tm: Year | Month (populated with all
dates required for reporting)

Example 1 without empty rows:

select
    Category
    ,month
    ,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year
    ,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year

from
    sales

where
    year in (2008,2007)

group by
    Category
    ,month

RETURNS:

Category  |  Month  |  Rev. This Year  |  Rev. Last Year
Bikes          1          10 000               0
Bikes          2          12 000               11 000
Bikes          3          12 000               11 500
Bikes          4          0                    15 400

Example 2 with empty rows:
I am going to use a sub query (but others may not) and will return an empty row for every product and year month combo.

select
    fill.Category
    ,fill.month
    ,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year
    ,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year

from
    sales
    Right join (select distinct  --try out left, right and cross joins to test results.
                   product
                   ,year
                   ,month
               from
                  sales --this ideally would be from a products table
                  cross join tm
               where
                    year in (2008,2007)) fill


where
    fill.year in (2008,2007)

group by
    fill.Category
    ,fill.month

RETURNS:

Category  |  Month  |  Rev. This Year  |  Rev. Last Year
Bikes          1          10 000               0
Bikes          2          12 000               11 000
Bikes          3          12 000               11 500
Bikes          4          0                    15 400
Bikes          5          0                    0
Bikes          6          0                    0
Bikes          7          0                    0
Bikes          8          0                    0

Note that most reporting tools will do this crosstab or matrix functionality, and now that i think of it SQL Server 2005 has pivot syntax that will do this as well.

Here are some additional resources.
CASE
https://web.archive.org/web/20210728081626/https://www.4guysfromrolla.com/webtech/102704-1.shtml
SQL SERVER 2005 PIVOT
http://msdn.microsoft.com/en-us/library/ms177410.aspx

回复收藏 0 原文

疏忽 2024-07-11 08:03:39

诀窍是执行 FULL JOIN，使用 ISNULL 从任一表中获取连接列。我通常将其包装到视图或派生表中，否则您还需要在 WHERE 子句中使用 ISNULL。

SELECT 
    Program,
    Month,
    ThisYearTotalRevenue,
    PriorYearTotalRevenue
FROM (
    SELECT 
        ISNULL(ThisYear.Program, PriorYear.Program) as Program,
        ISNULL(ThisYear.Month, PriorYear.Month),
        ISNULL(ThisYear.TotalRevenue, 0) as ThisYearTotalRevenue,
        ISNULL(PriorYear.TotalRevenue, 0) as PriorYearTotalRevenue
    FROM (
        SELECT Program, Month, SUM(TotalRevenue) as TotalRevenue 
        FROM PVMonthlyStatusReport 
        WHERE Year = @FinancialYear 
        GROUP BY Program, Month
    ) as ThisYear 
    FULL OUTER JOIN (
        SELECT Program, Month, SUM(TotalRevenue) as TotalRevenue 
        FROM PVMonthlyStatusReport 
        WHERE Year = (@FinancialYear - 1) 
        GROUP BY Program, Month
    ) as PriorYear ON
        ThisYear.Program = PriorYear.Program
        AND ThisYear.Month = PriorYear.Month
) as Revenue
WHERE 
    Program = 'Bikes'
ORDER BY 
    Month

这应该可以满足您的最低要求 - 2007 年或 2008 年或两者都有销售的行。要获取任一年份都没有销售额的行，您只需内连接到 1-12 数字表（您执行有其中之一，不是吗？）。

The trick is to do a FULL JOIN, with ISNULL's to get the joined columns from either table. I usually wrap this into a view or derived table, otherwise you need to use ISNULL in the WHERE clause as well.

SELECT 
    Program,
    Month,
    ThisYearTotalRevenue,
    PriorYearTotalRevenue
FROM (
    SELECT 
        ISNULL(ThisYear.Program, PriorYear.Program) as Program,
        ISNULL(ThisYear.Month, PriorYear.Month),
        ISNULL(ThisYear.TotalRevenue, 0) as ThisYearTotalRevenue,
        ISNULL(PriorYear.TotalRevenue, 0) as PriorYearTotalRevenue
    FROM (
        SELECT Program, Month, SUM(TotalRevenue) as TotalRevenue 
        FROM PVMonthlyStatusReport 
        WHERE Year = @FinancialYear 
        GROUP BY Program, Month
    ) as ThisYear 
    FULL OUTER JOIN (
        SELECT Program, Month, SUM(TotalRevenue) as TotalRevenue 
        FROM PVMonthlyStatusReport 
        WHERE Year = (@FinancialYear - 1) 
        GROUP BY Program, Month
    ) as PriorYear ON
        ThisYear.Program = PriorYear.Program
        AND ThisYear.Month = PriorYear.Month
) as Revenue
WHERE 
    Program = 'Bikes'
ORDER BY 
    Month

That should get you your minimum requirements - rows with sales in either 2007 or 2008, or both. To get rows with no sales in either year, you just need to INNER JOIN to a 1-12 numbers table (you do have one of those, don't you?).

回复收藏 0 原文

女皇必胜 2024-07-11 08:03:39

关于降价 - 是的，这令人沮丧。编辑器确实预览了我的 HTML 表格，但发布后它就消失了 - 因此必须从帖子中删除所有 HTML 格式...

@kcrumley 我认为我们已经得出了类似的结论。这个查询很容易变得非常难看。实际上，在阅读您的答案之前，我使用类似（但不同的方法）解决了这个问题。我有权在报告数据库上创建存储过程和函数。我创建了一个表值函数，接受产品类别和财政年度作为参数。在此基础上，该函数将填充一个包含 12 行的表。如果有可用的销售，则行将使用视图中的数据填充，否则该行将具有 0 值。

然后，我连接函数返回的两个表。因为我知道所有表都会有十二个漫游，所以分配更容易，并且我可以加入产品类别和月份：

SELECT 
    SP1.Program,
    SP1.Year,
    SP1.Month,
    SP1.TotalRevenue AS ThisYearRevenue,
    SP2.TotalRevenue AS LastYearRevenue
FROM GetFinancialYear(@Category, 'First Look',  2008) AS SP1 
     RIGHT JOIN GetFinancialYear(@Category, 'First Look',  2007) AS SP2 ON 
         SP1.Program = SP2.Program AND 
         SP1.Month = SP2.Month

我认为您的方法可能更干净一些，因为 GetFinancialYear 函数非常混乱！但至少它有效 - 这让我现在很高兴；）

About the markdown - Yeah that is frustrating. The editor did preview my HTML table, but after posting it was gone - So had to remove all HTML formatting from the post...

@kcrumley I think we've reached similar conclusions. This query easily gets real ugly. I actually solved this before reading your answer, using a similar (but yet different approach). I have access to create stored procedures and functions on the reporting database. I created a Table Valued function accepting a product category and a financial year as the parameter. Based on that the function will populate a table containing 12 rows. The rows will be populated with data from the view if any sales available, if not the row will have 0 values.

I then join the two tables returned by the functions. Since I know all tables will have twelve roves it's allot easier, and I can join on Product Category and Month:

SELECT 
    SP1.Program,
    SP1.Year,
    SP1.Month,
    SP1.TotalRevenue AS ThisYearRevenue,
    SP2.TotalRevenue AS LastYearRevenue
FROM GetFinancialYear(@Category, 'First Look',  2008) AS SP1 
     RIGHT JOIN GetFinancialYear(@Category, 'First Look',  2007) AS SP2 ON 
         SP1.Program = SP2.Program AND 
         SP1.Month = SP2.Month

I think your approach is probably a little cleaner as the GetFinancialYear function is quite messy! But at least it works - which makes me happy for now ;)

回复收藏 0 原文

小耗子 2024-07-11 08:03:39

我可能是错的，但您不应该使用完整的外连接而不是左连接吗？这样您将从两个表中获得“空”列。

http://en.wikipedia.org/wiki/Join_(SQL)#Full_outer_join< /a>

回复收藏 0 原文

ぇ气 2024-07-11 08:03:39

使用pivot和Dynamic Sql我们可以实现这个结果

SET NOCOUNT ON
IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
DROP TABLE #TEMP

;With cte(Category , Revenue  ,  Yearh  ,  [Month])
AS
(
SELECT 'Bikes', 10000, 2008,1 UNION ALL
SELECT 'Bikes', 12000, 2008,2 UNION ALL
SELECT 'Bikes', 12000, 2008,3 UNION ALL
SELECT 'Bikes', 15000, 2008,1 UNION ALL
SELECT 'Bikes', 11000, 2007,2 UNION ALL
SELECT 'Bikes', 11500, 2007,3 UNION ALL
SELECT 'Bikes', 15400, 2007,4
)
SELECT * INTO #Temp FROM cte

Declare @Column nvarchar(max),
        @Column2 nvarchar(max),
        @Sql nvarchar(max)


SELECT @Column=STUFF((SELECT DISTINCT ','+ 'ISNULL('+QUOTENAME(CAST(Yearh AS VArchar(10)))+','+'''0'''+')'+ 'AS '+ QUOTENAME(CAST(Yearh AS VArchar(10)))
FROM #Temp order by 1 desc FOR XML PATH ('')),1,1,'')

SELECT @Column2=STUFF((SELECT DISTINCT ','+ QUOTENAME(CAST(Yearh AS VArchar(10)))
FROM #Temp FOR XML PATH ('')),1,1,'')

SET @Sql= N'SELECT Category,[Month],'+ @Column +'FRom #Temp
            PIVOT
            (MIN(Revenue) FOR yearh IN ('+@Column2+')
            ) AS Pvt

            '
EXEC(@Sql)
Print @Sql

结果

Category    Month   2008    2007
----------------------------------
Bikes       1       10000   0
Bikes       2       12000   11000
Bikes       3       12000   11500
Bikes       4       0       15400

Using pivot and Dynamic Sql we can achieve this result

SET NOCOUNT ON
IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
DROP TABLE #TEMP

;With cte(Category , Revenue  ,  Yearh  ,  [Month])
AS
(
SELECT 'Bikes', 10000, 2008,1 UNION ALL
SELECT 'Bikes', 12000, 2008,2 UNION ALL
SELECT 'Bikes', 12000, 2008,3 UNION ALL
SELECT 'Bikes', 15000, 2008,1 UNION ALL
SELECT 'Bikes', 11000, 2007,2 UNION ALL
SELECT 'Bikes', 11500, 2007,3 UNION ALL
SELECT 'Bikes', 15400, 2007,4
)
SELECT * INTO #Temp FROM cte

Declare @Column nvarchar(max),
        @Column2 nvarchar(max),
        @Sql nvarchar(max)


SELECT @Column=STUFF((SELECT DISTINCT ','+ 'ISNULL('+QUOTENAME(CAST(Yearh AS VArchar(10)))+','+'''0'''+')'+ 'AS '+ QUOTENAME(CAST(Yearh AS VArchar(10)))
FROM #Temp order by 1 desc FOR XML PATH ('')),1,1,'')

SELECT @Column2=STUFF((SELECT DISTINCT ','+ QUOTENAME(CAST(Yearh AS VArchar(10)))
FROM #Temp FOR XML PATH ('')),1,1,'')

SET @Sql= N'SELECT Category,[Month],'+ @Column +'FRom #Temp
            PIVOT
            (MIN(Revenue) FOR yearh IN ('+@Column2+')
            ) AS Pvt

            '
EXEC(@Sql)
Print @Sql

Result

Category    Month   2008    2007
----------------------------------
Bikes       1       10000   0
Bikes       2       12000   11000
Bikes       3       12000   11500
Bikes       4       0       15400

回复收藏 0 原文

苍风燃霜 2024-07-11 08:03:39

@Christian——降价编辑器——呃；特别是当你的帖子的预览和最终版本不一致时......
@Christian -- 完全外连接 -- 完全外连接被 WHERE 子句中引用 SP1 的事实所否决，并且 WHERE 子句在 JOIN 之后应用。要对其中一个表进行过滤的完整外连接，您需要将 WHERE 子句放入子查询中，以便过滤发生在连接之前，或者尝试将所有 WHERE 条件构建到JOIN ON 子句，非常丑陋。嗯，实际上没有什么好方法可以做到这一点。

@乔纳斯：考虑到这一点：