douwo8140
2015-03-09 18:58
浏览 78
已采纳

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

图片转代码服务由CSDN问答提供 功能建议

我的表用户有树行为

  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; 
   
 
 

和表交易

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

现在我需要 计算所有“请求”的总和(每个用户可以拥有超过数百个“交易”记录,每4小时生成一次新记录) 我的树中的用户所以我会得到(括号中的请求总和),这是一个月的计数。

  me- 
 | 
 | _ John  (20)
 |  | 
 |  | _ John2(200)
 |  | _简(233)
 |  | _ George(3233)
 | 
 | _Alena(500)
 ... 
   
 
 

因此每个月我都需要查看请求总和 每个用户从当月的第一天到当月的最后一天。 必须尽可能快。 COuld有人帮助我吗? 谢谢

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • 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.

    已采纳该答案
    打赏 评论

相关推荐 更多相似问题