Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    4
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Arrow Store weekly/monthly/alltime data

    I have an arcade website where each game has a "Daily Plays" column in a SQL database and at the end of every day I add this value to a "All Time Plays" column and reset the "Daily Plays" column. I would now like to record the "Weekly Plays" (last 7 days) and "Monthly Plays" (last 30 days) for those games. What would be the best and most efficient way to do that?

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    Just do that in you SQL query.

    Since the definition of "last 7 days" and "last 30 days" changes each and every day, it would make no sense at all to have to keep updating records to make them reflect that status. For that matter, I don't understand the point of having "Daily Plays" and "All Time Plays" columns.

    If you have a DATETIME or TIMESTAMP column with the date and/or time of the game, that's all you need. If that column is not already indexed, add an index to your table for that column.

    Then:
    Code:
    // to get today's games -- equivalent to your Daily Plays:
    SELECT list, of, fields FROM table WHERE DATE(gameDateAndTime) = CURDATE();
    
    // to get last week of games, NOT including today:
    SELECT list, of, fields FROM table 
    WHERE DATE(gameDateAndTime) 
        BETWEEN = DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
        AND DATE_SUB(CURDATE(), INTERVAL 7 DAY) 
    
    // to get last 30 days of games, NOT including today:
    SELECT list, of, fields FROM table 
    WHERE DATE(gameDateAndTime) 
        BETWEEN = DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
        AND DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
    
    // to get All Time Plays -- all games, NOT including today:
    SELECT list, of, fields FROM table 
    WHERE DATE(gameDateAndTime) < CURDATE()
    Again, if that gameDateAndTime field is indexed, this will be, if anything, *MORE* efficient than your current scheme of adding a column.

    The MySQL DATE() function extracts the date-only from a DATETIME or TIMESTAMP value. The CURDATE() function returns, of course, the current date. And DATE_SUB() should be obvious.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jackkicker (01-15-2013)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    And, incidentally, if you don't want to keep coding those queries to get the desired range of dates, just create some VIEWs of your table.

    Example:
    Code:
    CREATE VIEW WeeklyPlaysView 
    AS
    SELECT * FROM yourtable 
    WHERE DATE(gameDateAndTime) 
        BETWEEN = DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
        AND DATE_SUB(CURDATE(), INTERVAL 7 DAY)
    And now you can just use those views in any other queries. For example:
    Code:
    SELECT playerID, COUNT(*) AS gamesPlayedInWeek
    FROM WeeklyPlaysView
    GROUP BY playerID
    ORDER BY gamesPlayedInWeek DESC
    LIMIT 10
    will get you the top 10 players (by number of games played) in the last week.

    And so on.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jackkicker (01-15-2013)

  • #4
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    4
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thank you for your reply.

    But I think my question wasn't clear. What I need to know is what games we're the most popular today, this week (last 7 days), this month (last 30 days) and all time from all visitors together.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    LOL! No, that was not AT ALL clear.

    So you want this all in a single MySQL query?

    All in one record? Or one record per time period?

    And when you say "this week", do you mean "including today" or "excluding today"? And ditto for "this month" and "all time"?

    Assuming you do mean "including today" and assuming you want all the values in a single record:
    Code:
    SELECT gamename, 
           SUM( IF(DATE(gamePlayDate) = CURDATE(), 1, 0 ) ) AS today,
           SUM( IF(DATE(gamePlayDate) >= DATE_SUB(CURDATE(), INTERVAL 6 DAY), 1, 0 ) ) AS thisweek,
           SUM( IF(DATE(gamePlayDate) >= DATE_SUB(CURDATE(), INTERVAL 29 DAY), 1, 0 ) ) AS thismonth,
           COUNT(*) AS alltime
    FROM yourtable
    GROUP BY gamename
    ORDER BY gamename
    *** BUT ***
    If you really want *THE* most popular game for each of those periods, then actually my code from Post #3 is what you want!

    You would create one VIEW for each of the four periods, in the same vein as the one VIEW I show, and then use a query akin to the second one I show in that post for each of the periods.

    There is no really effective way to get the most popular in each of those periods into a single query. Oh, it can be done, using a UNION. But that UNION won't really save you anything in performance over the four separate queries.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jackkicker (01-15-2013)

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    Oh, heck. Here's the UNION version. It's not bad. It's just no better than 4 separate queries.

    Code:
    SELECT 'today' AS period, gamename, COUNT(*) AS plays
    FROM table WHERE DATE(gameDateAndTime) = CURDATE()
    GROUP BY period,gamename ORDER BY plays DESC LIMIT 1
        UNION
    SELECT 'week' AS period, gamename, COUNT(*) AS plays
    WHERE DATE(gameDateAndTime) >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) 
    GROUP BY period,gamename ORDER BY plays DESC LIMIT 1
        UNION
    SELECT 'month' AS period, gamename, COUNT(*) AS plays
    WHERE DATE(gameDateAndTime) >= DATE_SUB(CURDATE(), INTERVAL 29 DAY) 
    GROUP BY period,gamename ORDER BY plays DESC LIMIT 1
        UNION
    SELECT 'alltime' AS period, gamename, COUNT(*) AS plays
    GROUP BY period,gamename ORDER BY plays DESC LIMIT 1
    That gets 1 record per "period" (today, week, month, alltime) that shows the most popular gamename for the period.

    Again, the numbers *ALL* include today's games.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jackkicker (01-15-2013)

  • #7
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    4
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thanks again for the reply!

    I'm sorry but since my English isn't very good I think you still didn't understand what I need.. I'll try stating it differently so you better understand.

    For example, for the game "Age of War" I would need to record that the game was played:

    - 55 times today
    - 354 times last 7 days
    - 1040 times last 30 days
    - 12891 times total since the game was added.

    And I would need to record that info for every single games. This way I would be able to sort the games to see which ones were most popular today, last 7 days, last 30 days and all time.

    So my question is what is the best and most efficient way to record that info into my SQL table? As of now I only record how many times a game was played by incrementing a counter every time a game is played, without using any timestamp.

    I guess I would need at least 30 columns (one for each of the last 30 days) + 1 column for all time plays?

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    OH NO! Terrible DB design!

    You simply want ONE RECORD for EACH TIME a game is played!

    For example:
    Code:
    CREATE TABLE games (
        gameID INT AUTO_INCREMENT PRIMARY KEY,
        gameName VARCHAR(100)
    ) ENGINE INNODB;
    
    CREATE TABLE players (
        playerID INT AUTO_INCREMENT PRIMARY KEY,
        playerName VARCHAR(100),
        password VARCHAR(30)
        ...
    ) ENGINE INNODB;
    
    CREATE TABLE gamesPlayed (
        playerID INT,
        gameID INT,
        whenPlayed TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT FOREIGN KEY playerID REFERENCES players(playerID),
        CONSTRAINT FOREIGN KEY gameID REFERENCES players(gameID)
    ) ENGINE INNODB;
    See it? You record each and every game, who played it, what game they played, and when they played it.

    *THEN* you can use my answer, above, to find the most popular game in any period.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jackkicker (01-15-2013)

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    I don't really recommend this alternative, but you *COULD* do something very similar to what you are now doing. Like this:
    Code:
    CREATE TABLE games (
        gameID INT AUTO_INCREMENT PRIMARY KEY,
        gameName VARCHAR(100)
    ) ENGINE INNODB;
    
    CREATE TABLE dailyGames (
        gameID INT,
        datePlayed DATE,
        playCount INT,
        PRIMARY KEY ( gameID, datePlayed )
        CONSTRAINT FOREIGN KEY gameID REFERENCES players(gameID)
    ) ENGINE INNODB;
    So now, each time a game is played, you do:
    Code:
    INSERT INTO dailyGames ( gameID, datePlayed, playCount )
    VALUES( 33, '2013-1-15', 1 )
    ON DUPLICATE KEY playCount = playCount + 1
    If this is the first time game #33 is played *TODAY*, that will add a new record for that game for today. If it is not the first time, it will bump the playCount by 1.

    Notice that this means you will have one record PER GAME PER DAY.

    And now what you are after is easy.

    For example, to get the 20 most popular games in the last 30 days:
    Code:
    SELECT G.gamename, D.gameid, SUM(playCount) AS totalPlays
    FROM games AS G, dailyGames AS D
    WHERE G.gameid = D.gameid
    AND D.gameDate >= DATE_SUB( CURDATE(), INTERVAL 29 DAY )
    GROUP BY G.gamename, D.gameid
    ORDER BY totalPlays DESC
    LIMIT 20
    Stop thinking of one record per game. That's a very very bad idea.

    AT THE MINIMUM, do as I show here: One record PER GAME PER DAY.

    My own preference would be for the complete audit trail of one record for each and every game played, by game, date, and player. But the scenario I show in this post is a reasonable second choice.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jackkicker (01-15-2013)

  • #10
    New to the CF scene
    Join Date
    Jan 2013
    Posts
    4
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Would you recommend your methods even if I have over 1500 games on my site with around 50 plays per day for each game? I get the impression that your method would create too many records and slow the database in the long run.. Don't you think?

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    So 75,000 records added, per day? Say 75,000,000 records in 3 years?

    Well, MySQL could certainly handle that with no problem.

    But if you don't really care about tracking games played *per player*, then okay, use my seond idea. That's only 1500 records added per day. So only 1.5 million in 3 years. That's not many, at all, to MySQL. Again, make sure you have the proper primary key:
    Code:
    PRIMARY KEY ( gameID, datePlayed )
    Or it might be better to reverse that, come to think of it:
    Code:
    PRIMARY KEY ( datePlayed, gameID )
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    jackkicker (01-16-2013)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •