时间戳

首页 > 解决方案 > SQL：您如何在仅参考给定年份的月份的情况下每月平均数据？

python - SQL：您如何在仅参考给定年份的月份的情况下每月平均数据？

问题描述

我正在为我的一个 Python 课程做一个项目，我试图获取给定年份的平均每月降雪量。在我的数据集中，收集的数据跨越了 2016 年到 2017 年，用于许多不同的气象站。

这只是为了用 SQLite 清理一些天气报告 .csv 文件。我已经设法将数据（传统上是 csv 格式）转换为内存中的 sqlite 格式，但是我的 SQL 已经生锈了，我无法让数据按照我想要的方式进行调用。我已经查看过，尝试WHERE DATE < '20170101'在按日期分组之前将数据与 a 分开，但我什至无法将数据与日期分开（可能是 SQL 如何查找日期以及我的日期如何打孔的问题，看起来像12/24/2017）。

这就是我要运行的

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE t (STATION, NAME, DATE, AWND, SNOW);")

with open('filteredData.csv','r') as fin:
    # csv.DictReader uses first line in file for column headings by default
    dr = csv.DictReader(fin) # comma is default delimiter
    to_db = [(i['STATION'], i['NAME'], i['DATE'], i['AWND'], i['SNOW']) for i in dr]

cur.executemany("INSERT INTO t (STATION, NAME, DATE, AWND, SNOW) VALUES (?, ?, ?, ?, ?)", to_db)
con.commit()
data = cur.execute("SELECT STATION, NAME, DATE, AWND, AVG(SNOW) FROM t GROUP BY STATION")

我一直在尝试在executeorexecutemany语句中添加一行来对所有数据条目进行排序并在一年之前过滤掉，就像这样

cur.executemany("INSERT INTO t (STATION, NAME, DATE, AWND, SNOW) VALUES (?, ?, ?, ?, ?) WHERE DATE < '20170101'", to_db)

我希望输出显示（现在）每个位置的 2016 年平均降雪量（仍在努力进一步区分每个位置的平均每月降雪量），但是当我添加上面的行时，我得到了一个错误。当我在没有WHERE语句的情况下运行代码时，代码处理得很好（并像我想要的那样输出回 csv），但只显示每个位置的平均值，而不考虑这些平均值的时间线。

对于那些好奇的人，我从中导入的 csv 中的日期格式如下所示：12/24/2017

编辑：我修改execute了变量中的语句data看起来像

Jan = cur.execute("SELECT STATION, NAME, DATE, AWND, AVG(SNOW) FROM t WHERE (DATE > '2016-01-01' AND DATE < '2016-02-01') GROUP BY STATION")

Jan 现在反映的是日期 的平均值，2016-01-01据2016-02-01记录，这似乎实际上取了 1 月份的平均降雪量并将其输出到 CSV。现在，我正在努力让二月在它之后打印而不覆盖它，其中，简单地用另一个变量调用另一个 writerows 似乎只是覆盖它。

标签： pythonsqlsqlite

解决方案

SELECT
    STATION
  , NAME
  , MIN(DATE)
  , AVG(AWND)
  , AVG(SNOW)
FROM
 t
WHERE 
 DATE < '1/1/17'
GROUP BY
 STATION

根据 SQL 92+ 标准，该 SQL 语句无效。
通常，当使用 GROUP BY 时，子句中使用的所有非聚合列SELECT也应该在GROUP BY子句中。
因此，该Name列也应该在GROUP BY子句中使用，但这会给您的问题带来无效的结果。

我相信您正在寻找此查询。

SELECT 
 t.*
FROM (
  SELECT 
      STATION
    , MIN(DATE) AS min_date
    , AVG(AWND) AS avg_awnd
    , AVG(SNOW) AS avg_snow
  FROM 
   t
  WHERE
   DATE < '1/1/17'
  GROUP BY 
    STATION
) AS t_aggregated
INNER JOIN
 t 
ON
   t_aggregated.STATION = t.STATION
 AND
   t_aggregated.min_date = t.date

推荐阅读