英文:
Calculating Increasing Based on Value in Column
问题
我有这样的列：
[before](https://i.stack.imgur.com/wshbW.png)
select date(created_at) datee, count(*) video_count
from test_ambil_nihongo_doang_loop_jan2023
where month(video_time_published) = 1 and year(video_time_published) = 2023
group by date(created_at)
我想要计算从video_count列中获取的增量值，所以我得到了一个名为increment的新列，就像这样
[after](https://i.stack.imgur.com/igLkE.png)
我使用的是MYSQL，请给我解决方案。谢谢
我期望在increment列中获得值
英文:
I have column like this :
select date(created_at) datee, count(*) video_count
from test_ambil_nihongo_doang_loop_jan2023
where month(video_time_published) = 1 and year(video_time_published) = 2023
group by date(created_at)
and i want to Calculating Increasing value from video_count column, so i get the new column called increment like this
im using MYSQL, please give me solution to solve this. thanks
im expecting to get value in increment columns
答案1
得分: 0
假设您有名为 id 的主键，也假设您希望 increment 列相对于前一天（不一定是前一行）进行增加，则这可能适用于您。
对于每一行，我们寻找具有前一天的 created_at 日期的同一表中的条目。因为这会使行数增加（在聚合之前），所以我们在计数中添加 DISTINCT 子句以避免重复计数。
选择
date(t.created_at) datee,
计数(DISTINCT t.id) video_count,
计数(DISTINCT t.id) - 计数(DISTINCT s.id) increment
从test_ambil_nihongo_doang_loop_jan2023 t中选择
左连接测试_ambil_nihongo_doang_loop_jan2023 s
在月份（s.video_time_published）= month(t.video_time_published)
并且年（s.video_time_published）= year(t.video_time_published)
并且日期（s.created_at）= date_sub(t.created_at, INTERVAL 1 DAY)
其中月份（t.video_time_published）= 1
年（t.video_time_published）= 2023
按日期（t.created_at）分组
英文:
Presuming you have a primary key called id, and also presuming that you want the increment column to be relative to the previous day (not necessarily the previous row), then this might work for you.
For each row, we look for entries from the same table that have a created_at date of the previous day. Because this will multiply the rows (before aggregation), we add DISTINCT clauses to the counts so we don't double count.
select
date(t.created_at) datee,
count(DISTINCT t.id) video_count,
count(DISTINCT t.id) - count(DISTINCT s.id) increment
from test_ambil_nihongo_doang_loop_jan2023 t
left join test_ambil_nihongo_doang_loop_jan2023 s
ON month(s.video_time_published) = month(t.video_time_published)
AND year(s.video_time_published) = year(t.video_time_published)
AND date(s.created_at) = date_sub(t.created_at, INTERVAL 1 DAY)
where month(t.video_time_published) = 1
and year(t.video_time_published) = 2023
group by date(t.created_at)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论