如何根据访问量获得最受欢迎的商品？

发布于 2024-12-07 21:27:15 字数 144 浏览 0 评论 0原文

假设我们有一个类似这样的项目表，

item_id
item_visits

我们应该向该表添加什么，以便能够根据这些项目在过去 7 天内的访问次数对这些项目进行排序。

我们如何向数据库查询它？

原文

Let's say we have a table for items that looks like this

item_id
item_visits

What should we add to this table, to be able to sort these items based on how many visits they have had in the last 7 days.

And how do we query that to the db?

分享到QQ

分享到微博

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

发布评论

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

夜访吸血鬼 2024-12-14 21:27:15

抱歉，我问错了...
嗯，当然，您需要添加一个名为 VisitTime 且类型为 datetime 的列。

之后，我建议创建一组视图来帮助您进行查询。例如，名为 DailyVisit 的视图，其中包含每个项目的日内视图量（由日期表示）。

回复收藏 0 原文

永言不败 2024-12-14 21:27:15

你的桌子有点混乱。

您不想跟踪项目访问次数。
您只想跟踪某个项目被访问的时间以及访问者、访问地点以及哪个项目。

我建议如下：

table visit
------------
id integer not null primary key auto_increment
item_id integer not null foreign key fk_item_id
        references item(id) on delete cascade on update cascade
visitor_id integer not null foreign key fk_visitor_id
        references visitor(id) on delete cascade on update cascade
visit_date timestamp not null default current_timestamp
engine = InnoDB

这应该为您提供了解每个项目的访问量并获取有关项目和访客的详细信息所需的所有详细信息。

-- select total number of visits per item for the last week.
SELECT 
  COUNT(*) as NrOfVisits
  , i.name as ItemName
FROM visit v
INNER JOIN item i ON (v.item_id = i.id)
WHERE v.visit_date BETWEEN now() AND DATE_SUB(now(), INTERVAL 7 DAY)
GROUP BY v.item_id WITH ROLLUP

Your table is a bit confused.

You don't want to keep track of the number of item visits.
You just want to keep track of when an item was visited and maybe by whom and and where and which item.

I would suggest the following:

table visit
------------
id integer not null primary key auto_increment
item_id integer not null foreign key fk_item_id
        references item(id) on delete cascade on update cascade
visitor_id integer not null foreign key fk_visitor_id
        references visitor(id) on delete cascade on update cascade
visit_date timestamp not null default current_timestamp
engine = InnoDB

This should give you all the details you need to know the visits per item and get details about items and visitors as well.

-- select total number of visits per item for the last week.
SELECT 
  COUNT(*) as NrOfVisits
  , i.name as ItemName
FROM visit v
INNER JOIN item i ON (v.item_id = i.id)
WHERE v.visit_date BETWEEN now() AND DATE_SUB(now(), INTERVAL 7 DAY)
GROUP BY v.item_id WITH ROLLUP

回复收藏 0 原文

~没有更多了~