英文:
How can I show the percentage of increase or decrease of visits to a page with respect to the previous year in SQL
问题
我需要一个在输入当前年份时显示相对于去年的访问量增长或减少百分比的SQL查询，如果可以的话，最好按月份进行。
我目前有这个查询，它显示了我的页面每月的访问量，我需要用一个新的查询填充百分比列：
SELECT
FORMAT([DateTime], 'MMMM') AS [月份],
COUNT(*) AS [访问者计数],
'' AS [百分比]
FROM
[BasicCore.VisitorCounter]
WHERE
YEAR([DateTime]) = 2023
GROUP BY
MONTH([DateTime]), FORMAT([DateTime], 'MMMM')
这是输出：
非常感谢。
英文:
how are you doing? I need a query in SQL that when entering the current year shows me a percentage of increase or decrease in visits to my page with respect to the previous year, if it can be better by months.
I currently have this query that shows me the number of visits per month on my page, I need to fill the Percentage column with a new query:
SELECT
FORMAT([DateTime], 'MMMM') AS [Month],
COUNT(*) AS [CounterOfVisitors],
'' AS [Percentage]
FROM
[BasicCore.VisitorCounter]
WHERE
YEAR([DateTime]) = 2023
GROUP BY
MONTH([DateTime]), FORMAT([DateTime], 'MMMM')
This is the output:
Thank you so much
答案1
得分: 1
你可以先扫描 2022 年和 2023 年的数据，然后在求和函数内部使用条件 CASE 进行年度对比。以下是示例代码：
SELECT
FORMAT([DateTime], 'MMMM') AS [Month],
SUM(CASE WHEN YEAR([DateTime])=2023 THEN 1 ELSE 0 END) AS [CounterOfVisitors],
(SUM(CASE WHEN YEAR([DateTime])=2023 THEN 1 ELSE 0 END) * 1.0 / NULLIF(SUM( CASE WHEN YEAR([DateTime])=2022 THEN 1 ELSE 0 END),0) - 1.0) AS [Percentage]
FROM
[BasicCore.VisitorCounter]
WHERE
YEAR([DateTime]) in (2022,2023)
GROUP BY
MONTH([DateTime]), FORMAT([DateTime], 'MMMM')
英文:
You can first scan both 2022 and 2023 data,and then use conditional CASE inside sum function to see year over year comparison.
See the following code as example.
SELECT
FORMAT([DateTime], 'MMMM') AS [Month],
SUM(CASE WHEN YEAR([DateTime])=2023 THEN 1 ELSE 0 END) AS [CounterOfVisitors],
(SUM(CASE WHEN YEAR([DateTime])=2023 THEN 1 ELSE 0 END) * 1.0 / NULLIF(SUM( CASE WHEN YEAR([DateTime])=2022 THEN 1 ELSE 0 END),0) - 1.0) AS [Percentage]
FROM
[BasicCore.VisitorCounter]
WHERE
YEAR([DateTime]) in (2022,2023)
GROUP BY
MONTH([DateTime]), FORMAT([DateTime], 'MMMM')
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论