表设计+ SQL问题 [英] table design + SQL question

问题描述

我有一个桌子食物棒，使用以下DDL创建。 （我使用mySQL 5.1.x）

  CREATE TABLE foodbar（
 id INT NOT NULL AUTO_INCREMENT，
 user_id INT NOT NULL，
 weight double not null，
 created_at date not null 
）; 
  

我有四个问题：

1. 如何编写返回
   a结果集的查询，该结果给出了
   以下信息：user_id，
   weight_gain其中weight_gain是
   权重之间的差异和
   a的重量，记录了7天
   以前。


2. 如何编写一个查询将
   返回顶部的N个用户
   最大的体重增加（再次超过
   a周）。一个'明显'的方式可能是
   使用问题1
   获得的查询作为子查询，但不知何故
   选择顶部N。


3. 由于在问题2（和确实
   问题1），我正在使用
   计算字段搜索表中的
   记录，索引将是
   优于优化查询
   ，因为它是一个计算的
   字段，不清楚哪个字段
   进行索引（我猜，'weight'
   字段是需要
   的字段索引）。假设我在
   foodbar表中有另一个字段（比如'height'），而
   想要从
   表中选择记录，根据（说）产品
   （即乘法）'height'
   和'weight' - 我会在
   中正确地假设我需要索引
   'height'和'weight'？我还要
   需要创建一个复合键（比如
   （height，weight））。如果这个问题
   不清楚，我会很高兴
   澄清

解决方案

我不明白为什么你需要合成键，所以我将使用这个表：

  CREATE TABLE foodbar（
 user_id INT NOT NULL 
，created_at date not null 
，weight double not null 
，PRIMARY KEY（user_id，created_at）
）; 
  

我如何编写返回给我的结果集的查询以下信息：user_id，weight_gain其中weight_gain是7天前记录的权重和权重之间的差异。

  SELECT curr.user_id，curr.weight  -  prev.weight 
 FROM foodbar curr，foodbar prev 
 WHERE curr.user_id = prev.user_id 
 AND curr.created_at = CURRENT_DATE 
 AND prev.created_at = CURRENT_DATE  -  INTERVAL'7天'
; 
  

日期算术语法可能是错误的，但您得到的想法

我如何编写一个查询，将返回最大的体重增加的最高N位用户（再说一个星期）。一个明显的方式可能是使用上述问题1中获得的查询作为子查询，但不知何故选择顶部的N。

见上文，添加 ORDER BY curr.weight - prev.weight DESC 和 LIMIT N

最后两个问题：不要推测，检查执行计划。 （postgresql有 EXPLAIN ANALYZE ，dunno关于mysql）你可能会发现你需要索引参与 WHERE 和 JOIN ，而不是形成结果集的那些。

I have a table foodbar, created with the following DDL. (I am using mySQL 5.1.x)

CREATE TABLE foodbar (
    id          INT NOT NULL AUTO_INCREMENT,
    user_id     INT NOT NULL,
    weight      double not null,
    created_at  date not null
);

I have four questions:

1. How may I write a query that returns a result set that gives me the following information: user_id, weight_gain where weight_gain is the difference between a weight and a weight that was recorded 7 days ago.
2. How may I write a query that will return the top N users with the biggest weight gain (again say over a week).? An 'obvious' way may be to use the query obtained in question 1 above as a subquery, but somehow picking the top N.
3. Since in question 2 (and indeed question 1), I am searching the records in the table using a calculated field, indexing would be preferable to optimise the query - however since it is a calculated field, it is not clear which field to index (I'm guessing the 'weight' field is the one that needs indexing). Am I right in that assumption?.
4. Assuming I had another field in the foodbar table (say 'height') and I wanted to select records from the table based on (say) the product (i.e. multiplication) of 'height' and 'weight' - would I be right in assuming again that I need to index 'height' and 'weight'?. Do I also need to create a composite key (say (height,weight)). If this question is not clear, I would be happy to clarify

解决方案

I don't see why you should need the synthetic key, so I'll use this table instead:

CREATE TABLE foodbar (
  user_id     INT NOT NULL
, created_at  date not null
, weight      double not null
, PRIMARY KEY (user_id, created_at)
);

How may I write a query that returns a result set that gives me the following information: user_id, weight_gain where weight_gain is the difference between a weight and a weight that was recorded 7 days ago.

SELECT curr.user_id, curr.weight - prev.weight
FROM foodbar curr, foodbar prev
WHERE curr.user_id = prev.user_id
  AND curr.created_at = CURRENT_DATE
  AND prev.created_at = CURRENT_DATE - INTERVAL '7 days'
;

the date arithmetic syntax is probably wrong but you get the idea

How may I write a query that will return the top N users with the biggest weight gain (again say over a week).? An 'obvious' way may be to use the query obtained in question 1 above as a subquery, but somehow picking the top N.

see above, add ORDER BY curr.weight - prev.weight DESC and LIMIT N

for the last two questions: don't speculate, examine execution plans. (postgresql has EXPLAIN ANALYZE, dunno about mysql) you'll probably find you need to index columns that participate in WHERE and JOIN, not the ones that form the result set.

这篇关于表设计+ SQL问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！