douwo8140
2015-03-09 18:58 阅读 64
已采纳

MySQL:在CakePHP中Tree行为中对表中字段求和的最快方法

I have table users with tree behavior

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `rght` int(11) DEFAULT NULL,
  `user_email` varchar(255) NOT NULL DEFAULT '',
  `user_password` char(100) NOT NULL DEFAULT '',
  `user_name` varchar(255) NOT NULL DEFAULT ''
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and table trades

CREATE TABLE `trades` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `requests` float DEFAULT NULL,
  `trade_date_start` datetime DEFAULT NULL,
  `trade_date_stop` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

and now I need to count sum of all 'requests' (each user can have more than hundreds of 'trades' records, new record is generated once per 4 hours) per user within my tree so I will get (sum of requests in parenthesis) and this is count in scope of one month.

me-
  |
  |_ John (20)
  |  |
  |  |_ John2 (200)
  |  |_ Jane (233)
  |  |_ George (3233)
  |
  |_ Alena (500)
...

So each month I need to see sum of request for each user from 1st day of the current month til last day of current month. It has to be as fast as possible. COuld somebody help me? Thank you

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    dqmchw0071 dqmchw0071 2015-03-09 19:39

    This problem begs for a solution via recursive query, which MySQL alone among commonly used DBMSs does not support. As a result, I think you'll need to perform the necessary recursion yourself if you're tied to MySQL. Each query might have this form:

    SELECT
      user_id,
      SUM(requests) AS requests,
    FROM
      users
      JOIN trades
        ON users.id = trades.user_id
    WHERE
      users.parent_id = <PARENT_ID>
      AND trade_date_start BETWEEN <WINDOW_START_TIMESTAMP> AND <WINDOW_END_TIMESTAMP>
    GROUP BY user_id
    

    You would need to process each result row and recursively issue the same sort of query for each user_id returned.

    Since you'll be issuing possibly many queries with the same date filtering condition, you might speed it up by first creating a temporary table containing just those rows from trades that fall in the window of interest, then using that (with no explicit date condition) instead of table trades.

    点赞 评论 复制链接分享

相关推荐