Mysql查询5,10分钟和1,2小时内的选择数据

I have a two date one is entry_user and exit_user i want display count of number of user spend time in showroom from last 5 minit , last 30 minit and last 1 hours..I want to make this result from mysql query

Please Suggest me to how i do this for display below result

thanks advance

i want result like this

date        visitor_count

5 (minit)     4
10(minit)     10
1(hours)      20

my table example:

Create auxiliary table intervals, and add to this your time intervals in seconds. For example

CREATE TABLE `intervals` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `analytics`.`intervals` (`value`) VALUES(0),(300),(600),(1800),(3600);

To display count of user number accordinging to duration of an user activity use following:

SELECT 
  `value` AS `Duration`, COUNT(*) AS `visitor_count`
FROM
  (
  SELECT
    UNIX_TIMESTAMP(exit_user)-UNIX_TIMESTAMP(entry_user) AS `Interval`
  FROM
    YourTable
  ) AS `Calculations` 
  JOIN intervals ON `Calculations`.`Interval` >= intervals.value
GROUP BY intervals.value

If you also want to group users by date of visit, then use

SELECT 
  `Date`, `value` AS `Duration`, COUNT(*) AS `visitor_count`
FROM
  (
  SELECT
    DATE(`entry_user`) AS `Date`,
    UNIX_TIMESTAMP(`exit_user`)-UNIX_TIMESTAMP(`entry_user`) AS `Interval`
  FROM
    YourTable
  ) AS `Calculations` 
  JOIN intervals ON `Calculations`.`Interval` >= intervals.value
GROUP BY `Calculations`.`Date`, intervals.value

Let there exists the table sessions with columns entry_user and exit_user. For example:

DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `entry_user` datetime NOT NULL,
  `exit_user` datetime NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `sessions` (entry_user, exit_user) 
VALUES 
    ('2016-01-01 00:00:00','2016-01-01 00:00:10'),
    ('2016-01-01 00:00:00','2016-01-01 00:03:00'),
    ('2016-01-01 00:00:00','2016-01-01 01:00:00'),
    ('2016-01-01 00:00:00','2016-01-01 00:07:00'),
    ('2016-01-01 00:00:00','2016-01-01 00:15:03'),
    ('2016-01-01 00:00:00','2016-01-01 00:06:00'),
    ('2016-01-01 00:00:00','2016-01-01 00:11:00'),
    ('2017-01-01 00:00:00','2017-01-01 00:11:00'),
    ('2017-01-01 00:00:00','2017-01-01 00:01:30');

Create auxiliary table ticks:

DROP TABLE IF EXISTS `ticks`;
CREATE TABLE `ticks` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

and add time ticks values in seconds:

INSERT INTO `ticks` (`value`) VALUES (0),(300),(600),(900),(1800),(3600);

Create view intervals

DROP TABLE IF EXISTS `intervals`;
CREATE VIEW intervals AS 
SELECT 
    s.`value` AS started, f.`value` AS finished 
FROM 
    ticks s JOIN ticks f ON s.ID = (f.ID - 1);

Now you can count sessions that durations more then specified ticks values, using

SELECT 
  `Date`, `value` AS `Duration more or equals then`, COUNT(*) AS `Visitors Number`
FROM
  (
  SELECT
    DATE(entry_user) AS `Date`,
    UNIX_TIMESTAMP(exit_user)-UNIX_TIMESTAMP(entry_user) AS Duration
  FROM
    sessions
  ) AS Durations 
  JOIN ticks ON Durations.Duration >= ticks.`value`
GROUP BY Durations.`Date`, ticks.`value`;

If you want to get density function of the session duration, use following:

SELECT 
  `Date`, started AS `Duration more or equals then`, finished AS `and less or equals then`, COUNT(*) AS `Visitors Number`
FROM
  (
  SELECT
    DATE(entry_user) AS `Date`,
    UNIX_TIMESTAMP(exit_user)-UNIX_TIMESTAMP(entry_user) AS Duration
  FROM
    sessions
  ) AS Durations 
  JOIN intervals ON Durations.Duration BETWEEN intervals.started AND intervals.finished
GROUP BY Durations.`Date`, intervals.`started`;