Создание нескольких столбцов средней цены для данных в sql

#sql #sqlite

Вопрос:

Я создал таблицу под названием «запасы» с информацией о ценах на акции для 5 разных автомобильных компаний за период в 6 дней. Для каждого дня цена акций была взята за 3 разных временных интервала. Я нашел способ закодировать среднюю цену акций каждой компании за 1 день, но я не уверен, как создать больше столбцов, чтобы у меня была средняя цена акций каждой компании за каждый день, т. Е. Столбцы будут: название, средняя цена 2020/09/08, средняя цена 2020/09/09, средняя цена 2020/09/10 и т.д. Таблица данных, которую я использую, содержит данные, собранные за 6 разных дней — часть из них показана ниже.

Мой код показан ниже, а выходные данные пока показаны в самом верху.

 SELECT name, SUBSTR(date_time, 1, 10) AS 'date', ROUND(AVG(price), 2) AS 'average price'
FROM stocks
WHERE SUBSTR(date_time, 1, 10) = '2020:09:08'
GROUP BY name;

1. В вашем примере данных указана только одна дата, поэтому немного непонятно, что вы хотите сделать.

2. Вверху показан вывод из моего кода, а внизу в таблице приведены мои примеры данных. Извините, это немного сбивает с толку

Ответ №1:

Вы можете сделать это с помощью условной агрегации:

 SELECT name, 
       ROUND(AVG(CASE WHEN SUBSTR(date_time, 1, 10) = '2020:09:08' THEN price END), 2) AS [average price 2020:09:08], 
       ROUND(AVG(CASE WHEN SUBSTR(date_time, 1, 10) = '2020:09:09' THEN price END), 2) AS [average price 2020:09:09], 
       ROUND(AVG(CASE WHEN SUBSTR(date_time, 1, 10) = '2020:09:10' THEN price END), 2) AS [average price 2020:09:10] 
FROM stocks
WHERE SUBSTR(date_time, 1, 10) BETWEEN '2020:09:08' AND '2020:09:10'
GROUP BY name;

Как и в вашем предыдущем вопросе, я советую вам изменить формат ваших дат на YYYY-MM-DD hh:mm:ss , чтобы вам не нужны были строковые функции для обработки дат.

Вы можете обновить таблицу следующим образом:

 UPDATE stocks
SET date_time = REPLACE(SUBSTR(date_time, 1, 10), ':', '-') || SUBSTR(date_time, -9)

Если вы обновите таблицу, то запрос может быть упрощен с помощью функции DATE() для извлечения части даты из datetimes:

 SELECT name, 
       ROUND(AVG(CASE WHEN DATE(date_time) = '2020-09-08' THEN price END), 2) AS [average price 2020:09:08], 
       ROUND(AVG(CASE WHEN DATE(date_time) = '2020-09-09' THEN price END), 2) AS [average price 2020:09:09], 
       ROUND(AVG(CASE WHEN DATE(date_time) = '2020-09-10' THEN price END), 2) AS [average price 2020:09:10] 
FROM stocks
WHERE DATE(date_time) BETWEEN '2020-09-08' AND '2020-09-10'
GROUP BY name;

1. Есть ли код, который я мог бы использовать для изменения формата моих дат вместо удаления всех записей и повторного ввода их вручную?

2. ВЫБЕРИТЕ name, ROUND(AVG(цена, ГДЕ date_time = ‘2020-09-08’), 2) как ‘средняя цена 2020-09-08’; —— Я попробовал этот код сейчас, чтобы заменить исходный ответ, который вы опубликовали, но я просто не могу заставить его работать; как изменение формата даты помогает мне найти среднее значение для каждой даты?

3. AVG(price WHERE date_time = '2020-09-08') синтаксически некорректно. Вам нужно выражение CASE. Проверьте мой запрос.

4. Если вы выполнили ОБНОВЛЕНИЕ, проверьте мой 2-й запрос.

5. @GuntashMangat в предложении WHERE моего 2-го запроса была опечатка. Проверьте сейчас.