在sql查询中计算距离（pythagoras）和运行计数 [英] Calculating distance (pythagoras) and running count in sql query

问题描述

我试图在SQL中构建一个相当复杂的查询，并且作为初学者，我将非常感谢一些帮助构建它。

我试图实现以下内容：

<1> /使用Cartesian纬度和经度坐标计算target_postcodes表中的邮政编码（例如E1 1AA）和population_postcodes表中的所有邮政编码之间的距离使用Pythagoras：

  SQRT（POW（MY_Y_AXIS  -  Y_AXIS，2）+ POW（MY_X_AXIS-X_AXIS，2））
  

2 /用这些距离值创建一个新列，

 不知道如何做到这一步
  



2-bis /通过 distanc对 population_postcodes 中的邮政编码进行排序e 我们获得的价值，



 不知道如何做到这一步
  UNTIL  。  code> running_count >  Number_of_beds 的E1 1AA，
 
 
 
运行计数 - 但缺少上述突破条件：
 
 
  SELECT距离，人口，
（SELECT sum（population_postcodes.Population ））AS总计
 
 FROM population_postcodes 
 WHERE population_postcodes.distance <= T1.distance）AS总计
 
 FROM population_postcodes AS T1 
  target_postcode ） 
和最后一个邮政编码的距离值添加到我们的运行计数。
 
 
最后，我需要循环查询整个 target_postcodes 
 code>表。
 
 
 
 nk you for you very much for help a newbie out！
解决方案
 <1>，2.将表格放在一起并执行操作他们，你需要使用Join 
  http：// dev。 mysql.com/doc/refman/5.0/en/join.html  
否则你的公式是正确的。要将其创建为查询中的列，只需将其写入投影（选择）部分即可。 
示例： 
 
 
  select 
 population_postcodes。*，
 target_postcodes。*，
）从population_postcodes获得距离
的SQRT（POW（population_postcodes.longitude- target_postcodes.longitude，2）+ POW（population_postcodes.latitude-target_postcodes.latitude，2））JOIN target_postcodes 
  
要点2之二。以column_name结尾，顺序asc / desc 
  http：/ /dev.mysql.com/doc/refman/5.0/en/sorting-rows.html  
 
 
 
 3.将所有内容写成子查询，然后只选择顶部查询中需要的内容。也请看 HAVING  
  http://dev.mysql.com/doc/refman/5.0/en/subqueries.html  
  http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html  
 
 
 
要点4.查看创建表格的方法并应用您所接收的内容 
 
 
  create table mytablename 
 select ...我的投影列
 from ... 
  
  http://dev.mysql.com/doc/ refman / 5.1 / en / create-table.html  
 
I am trying to build a rather complicated query in SQL, and being a beginner i would immensely appreciate some help to build it.

I am trying to achieve the following:





1/ Calculate the distance between a postcode in the target_postcodes table - say E1 1AA - and all the postcodes in the the population_postcodes table using Cartesian latitude and longitude coordinates using Pythagoras:
SQRT( POW(MY_Y_AXIS - Y_AXIS, 2) + POW(MY_X_AXIS-X_AXIS, 2) )
2/ Create a new column with those distance values,
not sure how to do that step
2-bis/ Sort postcodes in the population_postcodes by the distance value we obtained,
not sure how to do that step
3/ Beginning with the closest postcode, add the value in the population column to a running_count column UNTIL running_count > Number_of_beds of E1 1AA,

proposed query for running count - but missing the above breaking condition:
SELECT distance, Population,
 (SELECT sum(population_postcodes.Population)) AS Total

FROM population_postcodes
WHERE population_postcodes.distance <= T1.distance) AS Total

FROM population_postcodes AS T1
4/ Create a new table that contains the postcode E1 1AA (target_postcode) and the distance value of the last postcode added to our running count.

Finally, i would need to loop this query over the whole target_postcodes table.

Thank you for you very much for helping a newbie out!
解决方案
1., 2. To bring tables together and perform operations between them, you need to use Join
http://dev.mysql.com/doc/refman/5.0/en/join.html
otherwise your formula is correct. To create it as a column in your query, just write it in the projection(select) part.
Example:
select 
population_postcodes.*, 
target_postcodes.*, 
SQRT( POW(population_postcodes.longitude- target_postcodes.longitude, 2) + POW(population_postcodes.latitude-target_postcodes.latitude, 2) ) as distance
from population_postcodes JOIN target_postcodes
points 2 bis. End with Order by column_name asc/desc
http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html

point 3. Write everything as a sub-query, and select only what you need in the top query. Also look at HAVING
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html
http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

point 4. look at ways to create tables and apply what you nearned 
create table mytablename
select ... my projection columns
from ...
http://dev.mysql.com/doc/refman/5.1/en/create-table.html

                        
这篇关于在sql查询中计算距离（pythagoras）和运行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！