我有这个查询。
SELECT DATE_FORMAT(utcdt,GET_FORMAT(DATE,'ISO')) as utcdt,
HOUR(utcdt) as hour,
country,
sum(impressions) as impressions
FROM rtb_impressions where campaign_id='cid2204184260'
GROUP BY utcdt, hour, country
这些是结果.....
{'impressions': Decimal('1'), 'country': 'US', 'hour': 10L, 'utcdt': '2012-10-01'}
{'impressions': Decimal('40000'), 'country': 'US', 'hour': 9L, 'utcdt': '2012-10-02'}
{'impressions': Decimal('20000'), 'country': 'US', 'hour': 9L, 'utcdt': '2012-10-02'}
为什么我应该得到 2 行时得到了三行? 012-10-02 应该相加。
谢谢
你得到它是因为你按错误的值分组。试试这个:
SELECT DATE_FORMAT(utcdt,GET_FORMAT(DATE,'ISO')) as utcdt,
HOUR(utcdt) as hour,
country,
sum(impressions) as impressions
FROM rtb_impressions where campaign_id='cid2204184260'
GROUP BY DATE_FORMAT(utcdt,GET_FORMAT(DATE,'ISO')), hour, country
试试这个:
SELECT DATE_FORMAT(utcdt,GET_FORMAT(DATE,'ISO')) as utcdt,
HOUR(utcdt) as hour,
country,
sum(impressions) as impressions
FROM rtb_impressions
WHERE campaign_id='cid2204184260'
GROUP BY DATE_FORMAT(utcdt,GET_FORMAT(DATE,'ISO')), hour, country
如果要根据格式化值对结果进行分组,则需要确保在GROUP BY
子句中应用相同的格式。