MySQL根据另一个表的时间戳选择Info

EDIT: I have put this in both PHP and MySQL categories as I believe there may be a way to do this using multiple queries and to tie them together using PHP. I would rather not do this...but at the end of the day, a solution is a solution...

编辑:我已经把它放在PHP和MySQL类别中,因为我相信可能有一种方法可以使用多个查询来实现这一点并使用PHP将它们联系在一起。我宁愿不这样做......但在一天结束时,解决方案是一个解决方案......

This will hopefully be easy for someone to think about, but I am certainly stuck here! On my site, I allow users to post feeds/messages, and they are allowed to switch memberships as often as they would like. I would like to run some statistics based on the memberships they were when they posted each feed, rather than just their current membership. For example, I would like to see how many feeds were posted by each membership. Right now, I can run a count and join the users, feeds and memberships table, but this counts every feed by each user based on their current membership which is not necessarily what their membership was when they posted. I hope this makes sense.

希望有人能够轻松思考,但我肯定会被困在这里!在我的网站上,我允许用户发布提要/消息,并允许他们按照自己的意愿切换成员资格。我想根据发布每个Feed的成员资格运行一些统计信息,而不仅仅是他们当前的成员资格。例如,我想看看每个会员发布了多少个Feed。现在,我可以运行计数并加入用户,订阅源和成员资格表,但这会根据每个用户当前的成员资格计算每个订阅源,这不一定是他们发布时的成员资格。我希望这是有道理的。

As there are many feeds already in our DB, I am unable to add in a column to the feeds table showing which membership type the user who posted is. Here is what my tables (abbreviated) looks like to see if anyone has a query idea of how to do this:

由于我们的数据库中已有许多供稿,因此我无法在供稿表中添加一列,以显示发布的用户所属的成员资格类型。这是我的表(缩写)看起来是否有人查询如何执行此操作:

Users Table

id  username  membershipid
1   John Doe  1

Memberships Table

id  membershipname
1   Membership #1
2   Membership #2
3   Membership #3

Memberships History Table

会员历史表

id  membershipsid  usersid  unix_timestamp
1   1              1        1476635544.33
2   2              1        1476641890.11
3   3              1        1476642124.2
4   1              1        1476642161.51

Feeds Table

id  unix_timestamp     usersid
1   1476641716.809361  1
2   1476641783.866863  1
3   1476641822.779324  1
4   1476641904.066237  1
5   1476641973.767174  1
6   1476642182.821472  1

It is difficult to see quickly with the unix_timestamps...but what I would like is for a solution to provide this:

使用unix_timestamps很难快速看到......但我想要的是一个解决方案来提供这个:

Feed Count by Membership Table

按会员表计算的Feed数量

membershipid  feedcount
1             4
2             2
3             0

So far, I have tried many things but they all end up providing the current membership the user has...such as:

到目前为止,我已经尝试了很多东西,但它们最终都提供了用户拥有的当前成员资格......例如:

SELECT
    a.MembershipName MembershipName,
    COUNT(*) Feeds
FROM (SELECT
        m.membership_name MembershipName
    FROM feeds f
    JOIN users u ON f.usersid = u.id
        JOIN memberships m ON u.membership_id = m.id
    GROUP BY f.id
    ORDER BY f.unix_timestamp DESC) a
    GROUP BY a.MembershipName
ORDER BY a.MembershipName

But this does nothing with membership history table, so my output table is:

但这对会员历史表没有任何作用,所以我的输出表是:

Feed Count by Membership Table

按会员表计算的Feed数量

membershipid  feedcount
1             6
2             0
3             0

which is wrong, as it should be 1->4, 2->2 & 3->0 as shown in the table above. Any ideas from anyone?

这是错误的,因为它应该是1-> 4,2-> 2&3-> 0,如上表所示。任何人的想法?

1 个解决方案

#1

Note #1 - The query you want to perform is extremely expensive, my advice is to simply save the current membershipsid into the feeds table at the time the feed is posted

注意#1 - 您要执行的查询非常昂贵,我的建议是在发布订阅源时将当前的membershipsid保存到供稿表中

Note #2 - The query below assumes that the memberships history always contain at least one entry per user, not only when it is changed (so the first time it is assigned the historical record is created)

注意#2 - 下面的查询假定成员资格历史记录始终包含每个用户至少一个条目,而不仅仅是在更改时(因此第一次分配历史记录时)

The way I thought your solution is "how do I query the membership id value at time X?", the answer is simple, it is the most recent changed value BEFORE time X:

我认为你的解决方案的方式是“如何在时间X查询成员资格id值?”,答案很简单,它是在时间X之前最近更改的值:

SELECT h.membershipsid
FROM membershipshistory h
WHERE h.usersid = {USERID} AND h.unix_timestamp < {X}
ORDER BY h.unix_timestamp DESC
LIMIT 1

Next thing is "How do I list all the feeds together with the memberships ID where X is the time when the feed was posted?

接下来是“如何列出所有Feed以及成员资格ID,其中X是发布Feed的时间?

SELECT
  feeds.id,
  feeds.usersid,
  (
    SELECT h.membershipsid
    FROM membershipshistory h
    WHERE h.usersid = feeds.usersid AND h.unix_timestamp < feeds.unix_timestamp
    ORDER BY h.unix_timestamp DESC
    LIMIT 1
  ) AS historical_membershipsid
FROM
  feeds

Which outputs (from my sample data):

哪些输出(来自我的样本数据):

+------+---------+--------------------------+
| id   | usersid | historical_membershipsid |
+------+---------+--------------------------+
|    1 |       1 |                        1 |
|    2 |       1 |                        2 |
|    3 |       1 |                        3 |
+------+---------+--------------------------+

From this one your solution should be trivial, just put the whole query in a view or subquery and group by historical_membershipsid, but remember it is very expensive.

从这一点开始,您的解决方案应该是微不足道的,只需将整个查询放在视图或子查询中,并按history_membershipsid进行分组,但请记住它非常昂贵。

UPDATE

I apologize if the final solution was not that obvious, here is the final query to count feeds per membership using historical data:

如果最终解决方案不那么明显,我很抱歉,这是使用历史数据计算每个成员资格的最终查询:

SELECT
  hf.historical_membershipsid AS membershipsid,
  COUNT(hf.id) AS feedcount
FROM (
  SELECT
    feeds.id,
    feeds.usersid,
    (
      SELECT h.membershipsid
      FROM membershipshistory h
      WHERE h.usersid = feeds.usersid AND h.unix_timestamp < feeds.unix_timestamp
      ORDER BY h.unix_timestamp DESC
      LIMIT 1
    ) AS historical_membershipsid
  FROM
    feeds) AS hf
GROUP BY
  hf.id

#1