我想显示数据库中产品组的平均价格。因此,我想返回该产品组中所有产品随时间变化的平均价格。
首先,我想知道这个群体的价格发生了多少变化。此查询成功返回所有日期,使用此查询。。。
SELECT DISTINCT(DATE(gbp.prdate)) as dte
FROM sku AS s
INNER JOIN mt_price gbp ON s.id = gbp.
WHERE (gbp.prdate < NOW() - INTERVAL ? DAY) AND (s.grp = ?)`
然后,我在php中使用foreach生成以下查询。。。
$i = 1;
$qry = "SELECT s.id AS sid, s.sku, s.title AS s_title,";
foreach ($dates as $date) {
$qry .= "(SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE(?) ORDER BY prdate LIMIT 1) AS ?, ";
$datearr[] = $date;
}
$qry = substr($qry, 0, -2) . " ";
$qry .= "FROM sku AS s
WHERE (s.grp = ?)
GROUP BY s.id
ORDER BY s.id";
$results = $db->prepare($qry);
foreach($datearr as $darr) {
$results->bindParam($i++,$darr->dte,PDO::PARAM_STR);
$results->bindParam($i++,$darr->dte,PDO::PARAM_STR);
}
上面的一个例子给了我这个查询。。。
SELECT s.id AS sid, s.rid, s.sku, s.title AS s_title,
(SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE('2015-01-08') ORDER BY prdate LIMIT 1) AS '2015-01-08',
(SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE('2015-02-12') ORDER BY prdate LIMIT 1) AS '2015-02-12',
(SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE('2015-01-26') ORDER BY prdate LIMIT 1) AS '2015-01-26',
(SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE('2015-02-08') ORDER BY prdate LIMIT 1) AS '2015-02-08',
(SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE('2015-01-30') ORDER BY prdate LIMIT 1) AS '2015-01-30',
(SELECT price FROM price WHERE price.sid = s.id AND prdate <= DATE('2015-02-02') ORDER BY prdate LIMIT 1) AS '2015-02-02'
来自sku AS其中(s.cat="TLPT01")按s.id分组按s.id 订购
当我如上所述运行这个查询时,我得到了我所期望的,每个SKU只有一个价格(一直到LIMIT
),并且是正确的价格。然后,当DATE
发生变化时,它将正确显示SKU的价格。是我!
所以,我想要的是得到所有这些结果的平均值。如果我在s.id
之前使用AVG
,我会得到SKU的平均值,这显然不是一个有任何意义的结果。我想要返回所有SKU的平均价格(因此,GROUP BY
应该只给我一行信息)。当我把GROUP BY
放在子查询之前时,我只得到一个错误。
尝试按如下方式生成子查询(从price中选择AVG(price),其中price.sid=s.id AND prdate<=日期(?)按价格分组。sid)
我发现将子查询移动到LEFT JOIN
中是解决方案。然后我可以对这些JOIN返回的价格进行AVG
。我仍然不知道为什么我不能只AVG
子查询,但这种方法(虽然不是超级快)。。。作品
SELECT s.cat, s.id AS sid, s.rid, s.sku, s.title AS s_title, AVG(pr1.price)
FROM sku AS s
LEFT JOIN (SELECT sid, price, prdate FROM price WHERE prdate <= DATE('2014-12-21') ) AS pr1 ON s.id = pr1.sid
... <more from foreach, add more AVG on top line> ...
WHERE (s.cat = 'TLPT01')
GROUP BY cat
您没有对所说的内容进行编码每个日期的平均值l。在子查询上使用平均查询(简化):
select date, avg(avg_price) from (
select group_id, date, avg(price) avg_price
from mytable
group by group_id, date
) x
group by date
内部查询将为每个group_id和date生成一个平均值,而外部查询仅按日期对这些group_id/date值进行平均。