sql - 替代耗时循环进行年累计（YTM

问题描述

我有一个问题我一直试图解决很长时间，现在我求助于你。我有两个数据库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即, , , ... ,DB2RegionJanJan-FebJan-MarJan-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)

现在，如前所述，这工作得很好，但如果考虑到所有月份（即如果所有Jan, Jan-Feb, Jan-Mar, ... , Jan-Dec）都被执行，则需要两个 10 小时。第一次是所有地区的一月，第二次是所有地区的一月和二月，依此类推。在某些情况下，因为我什至可能将 Region 更改为更长的 Region 列表，所以我的 PC 内存不足。我试图通过改变以另一种方式做到这一点

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)List 是每次通过时的列表（即，如果循环在第 5 步[1,2,3,4,5]，它将是代码中pandas部分需要做的操作可以在SQL查询部分完成，而且对于所有地区和月份，整个代码运行时间为20秒（DB1.

因此，我的问题是：有没有更有效的方法来做到这一点？我的猜测是避免循环，但是如何？我的经验是这种循环，在这种情况下应该避免，这证明我是对的。很明显，使用(cd.MonthOfYear between 1 and @month)是问题的根源。

感谢一群聪明人的帮助！

标签： sqlpandas

sql - 替代耗时循环进行年累计（YTM – 逐月）测量

问题描述

解决方案

推荐阅读