Mysql – how to count distinct value per day with start time and end time

countdategroup byMySQLmysql-5.7

my title may seem a bit confusing but here's the context.

I have a table 1. It contains the reference code per connection to SSID with value of start time. Now I have a scenario wherein the user connects today and disconnects after 2days. So in the table it only has 1 record containing starttime which is today and endtime that contains 2days after today.

Now I want to extract a report of user connection per day. In this case when I group by on the reference code per day, it will only count as 1 for 3 days. but i want it to be counted as 1 per day. What are some ways that I can do this?

@MrBrownstone
The result of that query would be

username                 starttime         count(*)
445425760933             2019-02-05            1
445425760934             2019-02-05            1
445425760934             2019-02-06            1

This is not the result that I am looking for. Below is the result that I need:

username                 starttime         count(*)
445425760933             2019-02-05            1
445425760933             2019-02-06            1
445425760933             2019-02-07            1
445425760934             2019-02-05            1
445425760934             2019-02-06            1

Since the code 445425760933 was used until 2019-02-07

Best Answer

For MySQL 8+:

WITH RECURSIVE dates AS (
SELECT MIN(DATE(starttime)) `date` 
  FROM table1
UNION ALL
SELECT `date` + INTERVAL 1 DAY 
  FROM dates 
  WHERE `date` < ( SELECT MAX(DATE(endtime)) 
                   FROM table1 )
),
users AS (
SELECT DISTINCT username 
  FROM table1
)
SELECT /* DISTINCT */ users.username, dates.`date`, 1 `count(*)`
FROM users
JOIN dates
JOIN table1 ON table1.username = users.username
           AND dates.`date` BETWEEN DATE(table1.starttime) 
                                AND DATE(table1.endtime)

Related Solutions

MySQL: AVG of defined variable per month

Rewritten to only count logins after the last previous order:

-- Calculate the previous order date for each order
CREATE TEMPORARY TABLE Orders
(
OrderID INT NOT NULL PRIMARY KEY,
UserID  INT NOT NULL,
OrderDate   DATETIME NOT NULL,
Year    YEAR NOT NULL,
Month   TINYINT NOT NULL,
PreviousOrderDate   DATETIME
)
INSERT INTO Orders (OrderID, UserID, OrderDate, Year, Month, PreviousOrderDate)
    SELECT
        O2.orderid,
        O2.userid,
        FROM_UNIXTIME(O2.date) AS OrderDate,
        YEAR(FROM_UNIXTIME(O2.date)) AS Year,
        MONTH(FROM_UNIXTIME(O2.date)) AS Month,
        MAX(O1.date_time) AS PreviousOrderDate
    FROM
        xcart_orders AS O2
        LEFT JOIN xcart_orders AS O1 ON O2.userid = O1.userid AND O1.date_time < O2.date_time
    GROUP BY
        O2.orderid,
        O2.userid,
        O2.date

-- Calculate the average for each year and month
SELECT
    Year, Month, COUNT(*) AS Orders, SUM(PreviousLogins) / COUNT(*) AS AvgPrevLogins
FROM
    (
    -- Get the number of previous logins for each order
    SELECT
        O.OrderID, O.Year, O.Month, COUNT(L.userid) AS PreviousLogins
    FROM
        Orders AS O
        LEFT JOIN xcart_login_history AS L
            ON  O.UserID = L.userid
            -- Filter logins here in the join rather than in a WHERE clause, or you exclude orders that HAVE no previous logins (in practice, such may not exist)
            AND L.date < O.OrderDate
            AND (L.date > O.PreviousOrderDate OR O.PreviousOrderDate IS NULL)  -- Only count logins from after the last previous order (if one exists)
            AND L.status = 'success'
            AND L.action IN ('login', 'autologin')
    GROUP BY
        O.OrderID, O.Year, O.Month
    ) AS X
GROUP BY
    Year, Month
ORDER BY
    Year, Month

Mysql – Difficulty with nested query order & grouping

This sort of thing can be done fairly elegantly using Window Functions. While I was researching this answer, I found that MySQL doesn't support them. The code that follows is will likely have some performance problems since it uses nested GROUP BY select statements. Proceed with caution.

SELECT UserID
     -- potentially add a date column here
     , sum(Counter) Updates
FROM (   
     SELECT UserID
          , ObjectID
          --- potentially add a date column here
          , 1 Counter
     FROM Object_Update_Table OUT inner join 
          (
          SELECT ObjectID
               -- potentially add a date column here
               , max(timestamp)
          FROM Object_Update_Table OUT
          WHERE timestamp >= today 00:00:00
                AND timestamp < tomorrow
          -- if you add a date column, you'll need to add a GROUP BY clause with the date column here
          ) last_update_day ON OUT.ObjectID = last_update_day.ObjectID
                           AND OUT.timestamp = last_update_day.timestamp
     ) Join_Back_User
GROUP BY Join_Back_User.UserID
     --, potentially add a date column here

The case above only works for one day due to the WHERE clause in line 12 and 13. If you need to do this for more than one day at a time, you should add a function which returns the date to the inner-most sub-query and then carry that date in the Join_Back_User subquery to the outermost query...

Related Question