Как получить TONE из таблицы GDELT GKG из Google BigQuery?
SELECT
DATE,
EXTRACT(YEAR FROM DATE) AS year,
FIPS as Country,
LOCATIONS,
AVG(TONE) as Avg_Tone,
AVG(Positive Score) as PositiveS,
AVG(Negative Score) as NegativeS,
COUNT(*),
From `gdelt-bq.gdeltv2.gkg_partitioned`,
`gdelt-bq.extra.sourcesbycountry` country,
Where
DATE(_PARTITIONTIME) BETWEEN TIMESTAMP('2002-01-01') AND TIMESTAMP('2020-12-31')
AND SourceCommonName=country.Domain
AND Location like '%CH%'
GROUP BY Year,Country
ORDER BY Year,Country
Ссылка на книгу кодов: http://data.gdeltproject.org/documentation/GDELT-Global_Knowledge_Graph_Codebook-V2.1.pdf V1.5TONE имеет ТОН, Положительную оценку, Отрицательную оценку и так далее. Я хочу подсчитать средний тон по годам. Как получить его из Big Query.
Ответы 1
Сначала необходимо бросить и разделить поля.
date— это значение в формате «ггггммдд....». Поэтому я предлагаю преобразовать значение в виде строки и рассматривать первые четыре символа как год.Здесь нет столбца
V1.5TONE, а естьV2Tone. Он состоит из строки с несколькими цифровыми числами, разделенными запятой. Строка должна быть разделена в первую очередь. Затем каждому компоненту нужно привести десятичное число.Таблица
gdelt-bq.extra.sourcesbycountryдолжна отображать URL-адрес одной страны. Он дублирует страны на один URL! Чтобы исключить хотя бы какое-то повторяющееся значение, используется внутреннийselectс группой by.Чтобы получить значения, сгруппированные по году, стране, все остальные столбцы измерений необходимо закомментировать.
SELECT
#DATE,
substr(cast(date as string),0,4) AS year,
FIPS as Country,
#LOCATIONS,
AVG(cast(split(V2Tone,",")[safe_offset(0)] as decimal )) as Avg_Tone,
AVG(cast(split(V2Tone,",")[safe_offset(1)] as decimal )) as PositiveS,
AVG(cast(split(V2Tone,",")[safe_offset(2)] as decimal )) as NegativeS,
COUNT(*) as counts,
From `gdelt-bq.gdeltv2.gkg_partitioned`
left join
(SELECT Domain, FIPS
from `gdelt-bq.extra.sourcesbycountry`
group by 1,2) country
on SourceCommonName=country.Domain
Where
DATE(_PARTITIONTIME) BETWEEN DATE('2020-01-01') AND DATE('2020-01-31')
AND Locations like '%CH%'
GROUP BY Year,Country
ORDER BY Year,Country
Также в сегменте where: не смешивайте DATE и TIMESTAMP.
Данные нельзя получить до 2015 года? Но на веб-сайте blog.gdeltproject.org/the-datasets-of-gdelt-as-of-february-2016 GKG был продлен до 1979 года к осени 2016 года.
Этот набор данных начинается с 19 февраля 2015 г. (17 февраля 2015 г. содержит только 25 строк).
Пожалуйста, покажите в будущем еще несколько своих работ, прежде чем спрашивать. Предоставление запроса, который предоставляет все необходимые столбцы, является обязательным, и он должен быть работоспособным или указывать, какая часть является проблемой. V1.5TONE — это не колонка, это V2Tone, верно? Также подумайте о том, чтобы уменьшить объем запрашиваемых данных, если это возможно (для теста, чтобы помочь вам, я не хочу тратить 270 ГБ данных запроса)