mysql - 从 mysql 服务器的日期列中查找平均间隔日期

Question

这是我试图从中获取从上到下平均间隔的日期列。例如，第一个间隔在“2008-03-29”和“2009-04-04”之间会有所不同，下一个间隔将是“2009-04-04”和“2010-05-18”之间的天数和列表差异继续。 Click here to see the date list

这是我写的存储过程的一部分。

SELECT Cow_ID, Cow_Age, Cow_Breed, count(animal_id) as Numb_calves_born, count(Calf_ID) as numb_calves_weaned, calve_interval, Cow_Sire_Breed  FROM(
    SELECT 
        @cow_id:=cattle_info_tbl.dam_ID as Cow_ID,
        cattle_info_tbl.cow_age as Cow_Age,
        @cow_breed:=(select breed from cattle_info_tbl where animal_id=@cow_id) as Cow_Breed,
        animal_id IN (select animal_id from cattle_info_tbl where dam_id=@cow_id) as animal_id,
        #cattle_info_tbl.animal_id as Calf_ID,
        @cow_sire_id:=(select sire_id from cattle_info_tbl where animal_id=@cow_id) as Cow_Sire_Breed,
        #Where goes the problem
        (MAX(cattle_info_tbl.birth_date)-MIN(cattle_info_tbl.birth_date)/(SUM(CASE WHEN weaning_tbl.manage_code='T' Then 0.5 ELSE 1 END))) as calve_interval


    FROM cattle_info_tbl 
    INNER JOIN measurement_tbl ON (cattle_info_tbl.chaps_id = measurement_tbl.chaps_id) AND entry_type='W'
    INNER JOIN weaning_tbl ON weaning_tbl.chaps_id=cattle_info_tbl.chaps_id
    where cattle_info_tbl.herd_id = input_herd_id AND dam_id!='' AND manage_code=0
    order by dam_id

Answer 1

注意:添加另一个答案比编辑前一个答案更容易。

1. 您需要使用 JOINED“派生表”而不是“相关子查询”。你会发现这也更有效率。这里需要对一些值进行平均，所以派生表涉及分组依据。

2. 要使用将前一个值转移到下一行的技术，您必须交叉连接一些变量，不要将其注释掉。

3. order by 子句对该技术至关重要。在这里你必须使用包含 dam_id 和 birth_date 的组合顺序，否则你会得到一个垃圾结果。

希望这些查询能为您确定逻辑。第一个帮助显示每一行的详细逻辑。第二个显示连接前的“派生表”，第三个显示将派生表连接到源(明细)表的效果。

查询 1:

SELECT
      IF((t2.dam_id=@prev_dam), datediff(t2.birth_date,@prev_value), NULL) difference
    , @prev_dam
    , @prev_value
    , t2.dam_id
    , t2.birth_date
    , @prev_dam := t2.dam_id
    , @prev_value := t2.birth_date
FROM cattle_info_tbl t2
CROSS JOIN (SELECT @prev_dam:=null x, @prev_value:=str_to_date(NULL,'%Y-%M-%d') y) y
order by t2.dam_id, t2.birth_date

Results :

| difference | @prev_dam | @prev_value | dam_id | birth_date | @prev_dam := t2.dam_id | @prev_value := t2.birth_date |
|------------|-----------|-------------|--------|------------|------------------------|------------------------------|
|     (null) |    (null) |      (null) |  S6040 | 2008-04-30 |                  S6040 |                   2008-04-30 |
|        351 |     S6040 |  2008-04-30 |  S6040 | 2009-04-16 |                  S6040 |                   2009-04-16 |
|        336 |     S6040 |  2009-04-16 |  S6040 | 2010-03-18 |                  S6040 |                   2010-03-18 |
|     (null) |     S6040 |  2010-03-18 |  S6093 | 2008-04-04 |                  S6093 |                   2008-04-04 |
|        376 |     S6093 |  2008-04-04 |  S6093 | 2009-04-15 |                  S6093 |                   2009-04-15 |
|        353 |     S6093 |  2009-04-15 |  S6093 | 2010-04-03 |                  S6093 |                   2010-04-03 |
|        344 |     S6093 |  2010-04-03 |  S6093 | 2011-03-13 |                  S6093 |                   2011-03-13 |
|        444 |     S6093 |  2011-03-13 |  S6093 | 2012-05-30 |                  S6093 |                   2012-05-30 |
|        351 |     S6093 |  2012-05-30 |  S6093 | 2013-05-16 |                  S6093 |                   2013-05-16 |
|        362 |     S6093 |  2013-05-16 |  S6093 | 2014-05-13 |                  S6093 |                   2014-05-13 |
|     (null) |     S6093 |  2014-05-13 |  S6094 | 2008-03-29 |                  S6094 |                   2008-03-29 |
|        371 |     S6094 |  2008-03-29 |  S6094 | 2009-04-04 |                  S6094 |                   2009-04-04 |
|        409 |     S6094 |  2009-04-04 |  S6094 | 2010-05-18 |                  S6094 |                   2010-05-18 |
|        300 |     S6094 |  2010-05-18 |  S6094 | 2011-03-14 |                  S6094 |                   2011-03-14 |
|       1185 |     S6094 |  2011-03-14 |  S6094 | 2014-06-11 |                  S6094 |                   2014-06-11 |

查询 2:

SELECT dam_id, AVG(difference) age
FROM (
      SELECT
            IF((t2.dam_id=@prev_dam), datediff(t2.birth_date,@prev_value), NULL) difference
          , t2.dam_id
          , @prev_dam := t2.dam_id
          , @prev_value := t2.birth_date
      FROM cattle_info_tbl t2
      CROSS JOIN (SELECT @prev_dam:=null x, @prev_value:=str_to_date(NULL,'%Y-%M-%d') y) y
      ORDER BY t2.dam_id, t2.birth_date
     ) b
GROUP BY dam_id

Results :

| dam_id |      age |
|--------|----------|
|  S6040 |    343.5 |
|  S6093 | 371.6667 |
|  S6094 |   566.25 |

查询 3:

SELECT
       t1.dam_id as cow_id
     , av.age
FROM cattle_info_tbl t1
LEFT JOIN (
          SELECT dam_id, AVG(difference) age
          FROM (
                SELECT
                      IF((t2.dam_id=@prev_dam), datediff(t2.birth_date,@prev_value), NULL) difference
                    , t2.dam_id
                    , @prev_dam := t2.dam_id
                    , @prev_value := t2.birth_date
                FROM cattle_info_tbl t2
                CROSS JOIN (SELECT @prev_dam:=null x, @prev_value:=str_to_date(NULL,'%Y-%M-%d') y) y
                ORDER BY t2.dam_id, t2.birth_date
               ) b
          GROUP BY dam_id
          ) av ON t1.dam_id = av.dam_id
WHERE t1.herd_id = 'H38' AND t1.dam_id<>''

Results :

| cow_id |      age |
|--------|----------|
|  S6093 | 371.6667 |
|  S6093 | 371.6667 |
|  S6094 |   566.25 |
|  S6094 |   566.25 |
|  S6093 | 371.6667 |
|  S6040 |    343.5 |
|  S6094 |   566.25 |
|  S6093 | 371.6667 |
|  S6040 |    343.5 |
|  S6040 |    343.5 |
|  S6093 | 371.6667 |
|  S6094 |   566.25 |
|  S6093 | 371.6667 |
|  S6094 |   566.25 |
|  S6093 | 371.6667 |

注意:我认为您将 dam_id 与 cow_id 混淆会使一切变得复杂。这似乎不正确。我的猜测是 animal_id 更有可能是重新标记为 cow_id 的正确列。