Блог

У меня есть проблема, которую я долго пытался решить, и теперь я обращаюсь к вам. У меня есть две базы DB1 DB2 данных и несколько критериев для данных, которые мне нужно обработать:

 ExcludeDates=['2019-01-01', '2019-01-02', '2019-01-03', '2019-01-04','2019-04-19','2019-04-22','2019-05-01','2019-05-31','2019-06-07','2019-12-21','2019-12-22','2019-12-23','2019-12-24','2019-12-25','2019-12-26','2019-12-27','2019-12-28','2019-12-29','2019-12-30','2019-12-31']
Region = ['Region1','Region2', 'Region3', 'Region4', 'Region5', 'Region6']
months        = [1,2,3,4,5,6,7,8,9,10,11,12]
 

Теперь я хочу выполнить ряд вычислений с функциями, извлеченными из DB1 DB2 каждого Region накопленного за месяц и более, то есть Jan , Jan-Feb , Jan-Mar , … , Jan-Dec

Моя первоначальная идея, которая работает, но крайне неэффективна (в некоторых случаях выполнение занимает до 10 часов), заключалась в том, чтобы перебирать все подмножества года (выше показано как Jan , Jan-Feb , Jan-Mar , … , Jan-Dec ) и регионы в цикле, вложенном в другой цикл:

 Measure_ytm = []

for RegionName in Region:
    for month in months:
        year           = 2019
        month          = month
        System  = 'System1'
        SystemV = 'System1V'

        Query1 = """
        select 
        a.feature1
        ,a.feature2
        ,a.feature3
        from DB1 as a
        inner join DateTable1 as  cd on a.Date = cd.Date
        inner join TimeTable as ct on A.Time = ct.Time
        
        where 1=1 
        and a.feature6 = @System
        and cd. Year = @year
        and (cd.MonthOfYear between 1 and @month)
        """
        new_query = query1.replace('@feature6', '''   System   ''').replace('@year', 
        str(year)).replace('@month', str(month))

        with pymssql.connect(server=server, user=user, password=password, database='BD1') as conn:
            with conn.cursor(as_dict=True) as cursor:
                cursor.execute(new_query)        
                all_rows = cursor.fetchall()
                df1 = pd.DataFrame(all_rows)

        query2 = """
        select
        b.Year as [Year]
       ,b.mon as [Month]
       , a.TId as [SystemV] 
       ,a.Name as [RegionName]
       , v.Namn as [Description]
       , m.Namn as [Type]
        ,b.Value
        from DB2 as b
        inner join MTable as m on b.MId=m.Id
        inner join VTable as v on b.VId=v.Id
        inner join AMTable as  a on b.AMId=a.Id
        where 1=1
        and f.Year=@year
        and (f.Mon between 1 and @month)
        and b.TId=@SystemV
        """
        query2 = query2.replace('@SystemV', '''   SystemV   ''').replace('@year', 
        str(year)).replace('@month', str(month))

        with pymssql.connect(server=server, user=user, password=password, database='DB2') as conn:
            with conn.cursor(as_dict=True) as cursor:
                cursor.execute(new_query) 
                all_rows = cursor.fetchall()
                df2 = pd.DataFrame(all_rows)


### Follows a list of operations on the retrived dataframe df1 and df2 which result

        x  = <an array containing the YTM accumulated value, each value in the aray belonging to one Region>
       Measure_ytm.append(x)
 

Теперь, как упоминалось ранее, это работает просто отлично, но занимает два 10 часа, если учитываются все месяцы (т. Е. Если выполняются все Jan , Jan-Feb , Jan-Mar , … , Jan-Dec ). Первый проход — только январь для всех регионов, второй — январь и февраль для всех регионов и так далее. В некоторых случаях, поскольку я могу даже изменить регион на более длинный список регионов, на моем КОМПЬЮТЕРЕ заканчивается память.
Я попытался сделать это по-другому, изменив

 months        = [1,2,3,4,5,6,7,8,9,10,11,12]
 

Для

 months        = [[1],[1,2],[1,2,3],[1,2,3,4],[1,2,3,4,5],[1,2,3,4,5,6],…..,[1,2,3,4,5,6,7,8,9,10,11,12]]
 

И замена (cd.MonthOfYear between 1 and @month) на (cd.MonthOfYear in List) where List — это список на каждом проходе (т. Е. Если цикл находится на проходе 5, так и будет [1,2,3,4,5] , но это не решает проблему.
Теперь, в качестве примечания: если каждый раз требуется только 1 месяц, многие операции, которые необходимо выполнить в части кода pandas, могут быть выполнены в части SQL-запроса. И для всех регионов и месяцев выполнение всего кода занимает 20 секунд (в нем примерно 25 миллионов строк DB1 .

Таким образом, мой вопрос: есть ли более эффективный способ сделать это? Я предполагаю, что следует избегать циклов, но как? Мой опыт показывает, что в подобных случаях следует избегать цикла, и это доказывает, что я прав. И очевидно, что использование (cd.MonthOfYear between 1 and @month) является корнем проблемы.

Благодарен за любую помощь от сообщества ярких людей!

Добавил мой комментарий в качестве ответа — пожалуйста, отметьте, если это помогло вам. Спасибо

Похоже, что каждый набор данных представляет собой просто объединение данных за каждый месяц, например, январь-февраль — это объединение данных за январь и февраль. Если это так, то почему бы вам просто не выполнить запрос один раз за весь год, январь-декабрь, и включить месяц и регион в качестве столбцов в свой оператор select. Затем вы можете выбрать нужные записи из этого набора данных в своем коде, т.Е. Поместить цикл в свой код только при обработке df1 / df2, а не вокруг вашего SQL?