SQL: Как вы усредняете данные за месяц, ссылаясь только на месяцы данного года?
Я работаю над проектом для одного из моих классов Python и пытаюсь получить среднемесячный снегопад за данный год. В моем наборе данных собраны данные за период с 2016 по 2017 год для многих различных метеорологических аванпостов.
Это просто для очистки некоторых файлов .csv отчета о погоде с помощью SQLite. Мне удалось получить данные, традиционно в формате csv, в формате sqlite в памяти, но мой SQL ржавый, и я не могу получить данные для переадресации так, как я этого хочу. Я просмотрел, попытался разделить данные с помощью WHERE DATE < '20170101' перед группировкой по дате, но я даже не могу разделить данные по датам (возможно, проблема с тем, как 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")
и я пытался добавить строку в операторы execute или executemany, чтобы отсортировать все записи данных и отфильтровать до года, например так
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")
Теперь январь отражает среднее значение для дат с 2016-01-01 по 2016-02-01, которое, кстати, фактически берет средний снегопад за январь и выводит его в CSV. Теперь я работаю над тем, чтобы заставить февраль печатать после него, не перезаписывая его, из которых простой вызов другой записи с другой переменной, похоже, просто перезаписывает его.
Ответы 3
SELECT
STATION
, NAME
, MIN(DATE)
, AVG(AWND)
, AVG(SNOW)
FROM
t
WHERE
DATE < '1/1/17'
GROUP BY
STATION
Этот оператор SQL недействителен по стандартам SQL 92+.
Как правило, при использовании 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
Это то, что вы хотите?
select station, name, strftime('%Y-%m') as yyyymm,
avg(snow)
from t
group by station, name, strftime('%Y-%m');
Вы можете добавить пункт where, чтобы ограничить данные определенным периодом времени. Например, для 2016 года:
select station, name, strftime('%Y-%m', date) as yyyymm,
avg(snow)
from t
where date >= '2016-01-01' and
date < '2017-01-01'
group by station, name, strftime('%Y-%m', date);
Да, это очень близко к тому, что я ищу, но я надеялся ограничить его до 2016 года, а затем каждый месяц сообщать о среднем количестве снегопадов. Теперь у меня есть что-то близкое в моем коде, но я не могу заставить SQL вызывать среднее значение за один месяц без усреднения всего вызванного. Скоро сделаю здесь новый пост, дам ссылку, если и когда сделаю
Мне не нужно было strftime, как вам, просто в качестве заметки. Я только что позвонил туда, и это, похоже, сработало. Я мог бы попробовать использовать strftime и посмотреть, решит ли это проблему перезаписывания всего.
@СэмДеВито. . . Есть ли причина, по которой вы не приняли этот ответ?
Это не было неправильно или что-то в этом роде. Это был ответ, который я искал, но я ответил на свой вопрос, поэтому я выбрал свой в качестве ответа. Мой ответ действительно вписывается в нишу вопроса, получая средние значения за каждый месяц года, а затем распечатывая их обратно. Спасибо за участие, хотя :)
Как я уже говорил в комментариях ранее, это было близко к тому, что я искал, но не совсем. Вот почему я сам ответил на него и указал, что я там искал, чтобы любой, кто просматривал пост, имел представление о том, как я это решил.
Итак, немного поработав с другом над программой, мы оба поняли, что на самом деле нам нужно вызвать программу в цикл и выполнить ее, прежде чем зафиксировать ее в файле. Вот что мы написали:
with open("Average2016.csv",'w') as f:
writer = csv.writer(f)
writer.writerow(['STATION','NAME','DATE','AWND','SNOW'])
'''
Fun for loop for generating dates. This uses zfill to pad the dates to 2 decimals
and checks whether we are on Dec. or not. If we are, skip to next January.
Then we use an f-string to create a SQL command and execute it and then write
the return value into the CSV.
'''
for x in range(1, 13):
date1 = '2016-' + str(x).zfill(2) + '-01'
date2 = '2016-' + str(x + 1).zfill(2) + '-01'
if (x == 12):
date2 = '2017-01-01'
sqlCmd = f"SELECT STATION, NAME, DATE, AWND, AVG(SNOW) FROM t WHERE (DATE >= '{date1}' AND DATE < '{date2}') GROUP BY STATION"
db_val = cur.execute(sqlCmd)
writer.writerows(db_val)
Я хотел сказать, что это то, как я писал это в первую очередь, но я думаю, что это называется немного иначе, чем (откровенно беспорядочно), как я называл это раньше. Хотя всем остальным спасибо за помощь!
Извините, что запутал вас с min(DATE) и avg(AWND), они мне не нужны. Что я хочу сделать, так это просто взять мою текущую таблицу со всеми выбранными и поместить их таким образом, чтобы их среднемесячные значения были показаны за данный год. Я обновил свой вопрос, так что вы можете посмотреть на редактирование того, как я собираюсь это сделать сейчас.