import matplotlib.pyplot as plt
from src.common.config_loader import ConfigLoader
from src.common.data_base_wrapper import DataBaseWrapper
config = ConfigLoader.load()
db = DataBaseWrapper(config["db"])
x, y = db.fetch_data(
"""
select extract(isodow from created) as hour, avg(hits_count)
from posts
join post_tags
on posts.id = post_tags.post_id
and posts.type = 1 and created between '2020-01-01' and '2020-07-01'
and not posts.is_editorial
and post_tags.value in ('#лонг', '#лонгрид', '#long', '#longread')
group by hour
order by hour
""", None)
plt.bar(x, y, color='green')
plt.xlabel("День недели (Москва, GMT+3)")
plt.ylabel("Среднее количество просмотров лонгов")
plt.title("Среднее количество просмотров лонгов (январь-июнь 2020)")
plt.grid(True, axis='y')
plt.xticks(x)
plt.show()
from src.common.config_loader import ConfigLoader
from src.common.data_base_wrapper import DataBaseWrapper
# https://plotly.com/python/filled-area-plots/
config = ConfigLoader.load()
db = DataBaseWrapper(config["db"])
fig = go.Figure()
subsite_ids, subsite_names = db.fetch_data(
"""
select subsite_id, subsite_name
from posts
where type = 1 and subsite_type = 2
group by subsite_id, subsite_name
order by count(id) desc
limit 19
""",
None
)
for (subsite_id, subsite_name) in zip(subsite_ids, subsite_names):
x, y = db.fetch_data(
"""
with time_scale as (
select date_trunc('week', generate_series) as time_window
from generate_series('2018-06-06'::timestamp, '2020-06-22'::timestamp, '1 week'::interval)
order by time_window
)
select time_scale.time_window, coalesce(data.count, 0) as count
import matplotlib.pyplot as plt
from src.common.config_loader import ConfigLoader
from src.common.data_base_wrapper import DataBaseWrapper
config = ConfigLoader.load()
db = DataBaseWrapper(config["db"])
x, y = db.fetch_data(
"""
select extract(hour from created) as hour, count(*)
from posts
where type = 1 and created between '2020-01-01' and '2020-07-01'
and not is_editorial and subsite_type = 2
group by hour
order by hour
""", None)
plt.bar(x, y, color='green')
plt.xlabel("Время (Москва, GMT+3)")
plt.ylabel("Количество постов")
plt.title("Количество постов (январь-июнь 2020, UGC, подсайты)")
plt.grid(True, axis='y')
plt.xticks(x)
plt.show()
import matplotlib.pyplot as plt
from src.common.config_loader import ConfigLoader
from src.common.data_base_wrapper import DataBaseWrapper
config = ConfigLoader.load()
db = DataBaseWrapper(config["db"])
x, y = db.fetch_data(
"""
select extract(hour from created) as hour, avg(hits_count)
from posts
where type = 1 and created between '2020-01-01' and '2020-07-01'
group by hour
order by hour
""", None
)
plt.bar(x, y, color='green')
plt.xlabel("Время публикации (Москва, GMT+3)")
plt.ylabel("Среднее количество просмотров")
plt.title("Среднее количество просмотров (январь-июнь 2020)")
plt.grid(True, axis='y')
plt.xticks(x)
plt.show()