mysql - 查询数据库查找第N条记录

Question

我不知道如何写这个问题的标题，但我需要一个查询，返回具有特定值的 N 记录。

我的表超过 520 万条记录

记录类似于:

• session (字符串，主索引)
• customer_id(整数，已索引)
• 点击次数(整数，已索引)
• order_number(整数，已索引)
• date_entry(日期时间，已索引)
• 许多其他领域

我需要知道的是同一客户在下订单之前登录网站(不同 session )多少次(order_number 为 0，除非在该 session 期间下订单)

示例数据可以是(简化数据)

session | c_id | clicks | ord_num  |         entry        |abc     | 123  |   2    |    0     | 2012-08-01 00:00:00  |cde     | 456  |   2    |    0     | 2012-08-01 00:00:01  |efg     | 457  |   2    |    0     | 2012-08-01 00:00:02  |hij     | 123  |   5    |    0     | 2012-08-01 00:00:03  |kod     | 986  |  10    |    0     | 2012-08-01 00:00:04  |wdg     | 123  |   2    |  9876    | 2012-08-01 00:00:05  |qwe     | 123  |   2    |    0     | 2012-08-01 00:00:06  |wvr     | 986  |  12    |  8656    | 2012-08-01 00:00:07  |

What I want is a query that shows something similar to:

• entry - date entry
• tot_sess - total number of session
• tot_cust - total number of customers
• 1sess - customer1 with only one session
• 2sess - customers with 2 sessions
• 3sess - customers with 3 sessions
• 4sess - customers with 4 sessions
• more4sess - customers with more than 4 sessions
• order1sess - customers that ordered on the first session
• order2sess - customers that ordered on the second session
• order3sess - customers that ordered on the third session
• order4sess - customers that ordered on the fourth session
• orderMore4Sess - customers that ordered after the fourth session

entry         |tot_sess|tot_cust| 1sess | 2sess | 3sess | 4sess | more4sess | order1sess |  order2sess |  order3sess |  order4sess | orderMore4Sess | 2012-08-01    |    8   |   4    |   2   |   1   |   0   |   1   |    0      |       0    |    1        |      1      |      0      |      0         |

I am already able to get the information about the session with the following query:

SELECT
   t.`date_entry`,
   COUNT(sess) `cust`,
   SUM(sess) `session`,
   COUNT(IF(sess>1,sess,NULL)) `more than once`,
   COUNT(IF(sess=1,sess,NULL)) `one`,
   COUNT(IF(sess=2,sess,NULL)) `two`,
   COUNT(IF(sess=3,sess,NULL)) `three`,
   COUNT(IF(sess=4,sess,NULL)) `four`,
   COUNT(IF(sess>4,sess,NULL)) `more`,
   ROUND(COUNT(IF(sess>1,sess,NULL))/COUNT(sess),2) `perc > 1`,
   ROUND(COUNT(IF(sess>2,sess,NULL))/COUNT(sess),2) `perc > 2`,
   ROUND(COUNT(IF(sess>3,sess,NULL))/COUNT(sess),2) `perc > 3`,
   ROUND(COUNT(IF(sess>4,sess,NULL))/COUNT(sess),2) `perc > 4`
FROM
(
SELECT
   `customer_id`,
   COUNT(`session`) `sess`,
   DATE(`date_entry`) `date_entry`
FROM `customer_activity_log`
WHERE
   `clicks` > 1
   AND `customer_id` > 0
   AND `date_entry` > '2012-08-01'
   AND subsite_id <=1
GROUP BY `date_entry`, `customer_id`
) t
GROUP BY date_entry

一旦我知道了，我还需要以不同的方式查看数据，例如，如果客户 123 在 2012 年 1 月 1 日首次出现，然后回来 15 次并在 2012 年下订单-08-01，然后又回来了 5 次，并在 2012 年 10 月 12 日下了另一个订单，我需要一个不按日期限制而仅按客户限制的查询，换句话说，限制 date_entry将被删除

希望这是有道理的

Answer 1

SELECT e               AS entry,
       SUM(sessions)   AS tot_sess,
       COUNT(*)        AS tot_cust,
       SUM(sessions=1) AS 1sess,
       SUM(sessions=2) AS 2sess,
       SUM(sessions=3) AS 3sess,
       SUM(sessions=4) AS 4sess,
       SUM(sessions>4) AS more4sess,
       SUM(orders  =1) AS order1sess,
       SUM(orders  =2) AS order2sess,
       SUM(orders  =3) AS order3sess,
       SUM(orders  =4) AS order4sess,
       SUM(orders  >4) AS orderMore4Sess
FROM (
  SELECT b.e, b.c_id, b.sessions, COUNT(a.entry) AS orders
  FROM   customer_activity_log a RIGHT JOIN (
    SELECT   DATE(entry) AS e, c_id, COUNT(*) AS sessions,
             MIN(IF(ord_num=0,NULL,entry)) AS o
    FROM     customer_activity_log
    GROUP BY e, c_id
  ) b ON a.c_id = b.c_id AND DATE(a.entry) = b.e AND a.entry <= b.o
  GROUP BY b.e, b.c_id
) t

查看 sqlfiddle .