我试图为两个问题找到一个优雅的解决方案:我在这两个问题上都对 SELECT 部分有问题。查询的另一部分很好
这些问题理论上是没有可用的数据库来运行它。
Q1:拉取以下报告:对于每天显示累计 (移动(过去 5 天内(含(的"赞"数量(例如:5 月 5 日 将显示 5 月 1 日至 5 月 5 日期间的总赞数。 拆分为美国与 非美国地理位置。
列:
数据时间戳 - 5 天时间范围的上限 总计 - 数量 时间范围内的点赞数 Region_US - 喜欢的国家/地区的数量 ="美国"Region_rest - 国家/地区<>"美国"的喜欢数
Q2:每天拉动创建的用户数和每日更改 与前一天相比。
代码:
SELECT
DATEADD(day, 4, se.date) AS DataTimstamp,
COUNT(se.type_id) AS Total,
COUNT(CASE WHEN lo.country_3_character_code = 'USA' THEN 1 ELSE NULL END) AS 'Region_USA',
COUNT(CASE WHEN lo.country_3_character_code != 'USA' THEN 1 ELSE NULL END) AS 'Region_rest'
FROM system_events se
JOIN location lo ON se.location_id = lo.id
WHERE se.type = 'like'
GROUP BY 1
SELECT u.creation_date AS 'day',
COUNT(IF(day = u.creation_date, u.id, 0)) AS Date_day,
COUNT(IF(day = u.creation_date - interval 1 day , u.id, 0)) AS Date_before,
SUM(SUM(Date_day)-SUM(Date_before)) AS daily_change
FROM user u
GROUP BY 1;
对于第二季度,我认为您可以使用 LAG 来获取前一天,例如这个简单的例子:
with cte_table as
(
select * from
(values
('01-Jan-19',1)
,('02-Jan-19',2)
,('03-Jan-19',3)
,('04-Jan-19',4)
,('05-Jan-19',5)
) as t (TheDate,TheValue))
select TheDate, TheValue,
LAG(TheValue,1,0) OVER(ORDER BY TheDate) as Prev_Value
from cte_table;
在过去的 5 天第一季度,我已经对此进行了一次打击,我可能会被 SQL 大师活活吃掉:-o,可能与交叉应用有关,但超出了我目前的知识范围。
with cte_table as
(
select * from
(values
('01-Jan-19',10)
,('02-Jan-19',20)
,('03-Jan-19',10)
,('04-Jan-19',5)
,('05-Jan-19',10)
,('06-Jan-19',20)
,('07-Jan-19',10)
,('08-Jan-19',10)
,('09-Jan-19',10)
,('10-Jan-19',5)
) as t (TheDate,TheValue))
, cte_table2 as
(
select TheDate, TheValue
,LAG(TheValue,1,0) OVER(ORDER BY TheDate) as Prev_Value1
,LAG(TheValue,2,0) OVER(ORDER BY TheDate) as Prev_Value2
,LAG(TheValue,3,0) OVER(ORDER BY TheDate) as Prev_Value3
,LAG(TheValue,4,0) OVER(ORDER BY TheDate) as Prev_Value4
from cte_table
)
select TheDate
,sum(TheValue) as current_day
, sum(TheValue) + sum(Prev_Value1) + sum(Prev_Value2) + sum(Prev_Value3) + sum(Prev_Value4) as [last 5 days]
from cte_table2
group by TheDate
order by TheDate
;