我正在努力可视化我应该做的这个查询。我觉得我应该将两个表连接在一起,但我迷失了使用什么将数据完全连接在一起。
Channel_Statistics
表
我看到channel_views
和clicks
都与channel_id
有关.
Video_Channel
表
channel_dancer_id
与channel_id
有关。channel_dancer_id
是Dancers
表中dancer_id
的 fk。
Dancers
表
dancer_id
链接到dancers_name
.
CREATE TABLE Dancers (
dancers_id int NOT NULL,
dancers_name varchar(255) NOT NULL,
PRIMARY KEY (dancers_id)
);
CREATE TABLE Video_Channel (
channel_id int NOT NULL,
channel_name varchar(255) NOT NULL,
channel_dancer_id int,
PRIMARY KEY (channel_id),
FOREIGN KEY (channel_dancer_id) REFERENCES Dancers(dancer_id)
);
CREATE TABLE Channel_Statistics (
channel_id int NOT NULL,
_date date,
channel_views int,
clicks int,
country varchar(255),
FOREIGN KEY (channel_id) REFERENCES Video_Channel(channel_id)
);
我尝试过什么
SELECT COUNT(Channel_Statistics.channel_views), COUNT(Channel_Statistics.clicks), Dancers.dance_name
FROM Channel_Statistics
INNER JOIN Video_Channel ON Channel_Statistics.channel_id=Video_Channel.channel_id
INNER JOIN Dancers ON Video_Channel.channel_dancer_id=Dancers.dancer_id
WHERE _date > NOW() - INTERVAL 7 DAY
GROUP BY Dancers.dancer_name;
您几乎接近正确的查询。 使用between
获取间隔 -7 天
select count(t3.channel_views), count(t3.clicks), t2.dance_name
from Video_Channel t1
join Dancers t2 on t2.dancers_id= t1.channel_dancer_id
join Channel_Statistics t3 on t3.channel_id = t1.channel_id
where _date between now() - interval 7 day and now()
group by t2.dance_name