In [ ]:

    

"""
For every day in a pgtable, count the number of rows for each interest entity.
At the end, calculate the mean between every day for each entity.
"""


    

In [ ]:

    

from senpy.pgsql.q.mean import meandays_by_entity


    

In [3]:

    

# Input parameters
conParam = {
    'HOST' : 'localhost', 'USER' : 'postgres', 'PASSWORD' : 'admin',
    'PORT' : '5432', 'DATABASE' : 'valcarris_v2'
}

pgtable = 'id_pares_od'
DAY_FIELD = 'dia'
ENTITY_FIELD = 'paragem'

COUNT_FIELD_NAME = 'validacoes'

# Dias uteis
EXCLUDE_DAYS = [
    '2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04', '2018-01-05', '2018-01-06', '2018-01-07',
    '2018-01-08', '2018-01-09', '2018-01-10', '2018-01-11', '2018-01-12',
    '2018-01-13', '2018-01-14', '2018-01-20', '2018-01-21',
    '2018-01-22', '2018-01-23', '2018-01-24', '2018-01-25', '2018-01-26',
    '2018-01-27', '2018-01-28', '2018-01-29', '2018-01-30', '2018-01-31', '2018-02-01'
]

# Sabados
"""EXCLUDE_DAYS = [
    '2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04', '2018-01-05', '2018-01-06', '2018-01-07',
    '2018-01-08', '2018-01-09', '2018-01-10', '2018-01-11', '2018-01-12',
    '2018-01-14', '2018-01-15', '2018-01-16', '2018-01-17', '2018-01-18', '2018-01-19',
    '2018-01-21', '2018-01-22', '2018-01-23', '2018-01-24', '2018-01-25', '2018-01-26',
    '2018-01-28', '2018-01-29', '2018-01-30', '2018-01-31',
    '2018-02-01'
]"""

# Domingos
"""EXCLUDE_DAYS = [
    '2018-01-01', '2018-01-02', '2018-01-03', '2018-01-04', '2018-01-05', '2018-01-06', '2018-01-07',
    '2018-01-08', '2018-01-09', '2018-01-10', '2018-01-11', '2018-01-12',
    '2018-01-13', '2018-01-15', '2018-01-16', '2018-01-17', '2018-01-18', '2018-01-19',
    '2018-01-20', '2018-01-22', '2018-01-23', '2018-01-24', '2018-01-25', '2018-01-26',
    '2018-01-27', '2018-01-29', '2018-01-30', '2018-01-31',
    '2018-02-01'
]"""

OUTPUT_FILE = r'D:\TRENMO_JASP\xyz\teste_dias_na_bd.xlsx'


    

In [ ]:

    

meandays_by_entity(
    conParam, 'valida_26b', DAY_FIELD, 'fid', COUNT_FIELD_NAME, OUTPUT_FILE, EXCLUDE_DAYS=EXCLUDE_DAYS
)


    

In [2]:

    

from senpy.pgsql.q.mean import meanrowsday_by_entity


    

In [7]:

    

INCLUDE_DAYS = [
    #'2018-01-08', '2018-01-09', '2018-01-10', '2018-01-11', '2018-01-12',
    '2018-01-15', '2018-01-16', '2018-01-17', '2018-01-18', '2018-01-19',
    #'2018-01-22', '2018-01-23', '2018-01-24', '2018-01-25', '2018-01-26',
    #'2018-01-29', '2018-01-30', '2018-01-31'
]

meanrowsday_by_entity(
    conParam, pgtable, DAY_FIELD, ["fid_percurso", "paragem"],
    r'D:\TRENMO_JASP\xyz\teste_dias_def.xlsx',
    newMeanField="validacoes",
    filterData="({}) AND fid_percurso LIKE '%26B%'".format(" OR ".join(["dia='{}'".format(d) for d in INCLUDE_DAYS])),
    numberDays=5
)


    

    




SELECT fid_percurso, paragem, (SUM(conta) / nday) AS validacoes FROM (SELECT fid_percurso, paragem, dia, COUNT(fid_percurso) AS conta FROM id_pares_od WHERE (dia='2018-01-15' OR dia='2018-01-16' OR dia='2018-01-17' OR dia='2018-01-18' OR dia='2018-01-19') AND fid_percurso LIKE '%26B%' GROUP BY fid_percurso, paragem, dia) AS foo, (SELECT 5 AS nday) AS foo2 GROUP BY fid_percurso, paragem, nday






    
Out[7]:





'D:\\TRENMO_JASP\\xyz\\teste_dias_def.xlsx'

In [ ]: