Блог

Проблема: существует более 100 таблиц для ОБЪЕДИНЕНИЯ ВСЕХ. Единственное отличие в имени — это дата. Вместо того, чтобы писать более 100 блоков кода и изменять только дату, имеет смысл написать скрипт на python для вывода кода sql:

Наш SQL-запрос, запрашивающий одну таблицу (т. Е. один финансовый квартал):

 SELECT 
  (SELECT
      PCR.repdte
      FROM 
      All_Reports_19921231_Performance_and_Condition_Ratios as 
      PCR) AS Quarter,
  (SELECT 
      Round(AVG(PCR.lnlsdepr))
      FROM 
      All_Reports_19921231_Performance_and_Condition_Ratios as 
      PCR) AS NetLoansAndLeasesToDeposits,
  (SELECT sum(CAST(LD.IDdepsam as int))
      FROM 
     'All_Reports_19921231_Deposits_Based_on_the_Dollars250,000
     _Reporting_Threshold' AS LD) AS 
     DepositAccountsWith$LessThan$250k
  

Соглашение об именовании каждого имени файла включает дату (финансовый квартал)

 All_Reports_19921231_Performance_and_Condition_Ratios
All_Reports_19921231_Performance_and_Condition_Ratios
All_Reports_19921231_Deposits_Based_on_the_Dollars250,000
_Reporting_Threshold
  

Мы хотим запросить все финансовые кварталы, включая 19921231 по настоящее время

     19921231
    19930331
    19930630 
    19930930
    19931231
    19940331
    19940630
    19940930
    19941231
    19950331
    19950630
    19950930
    19951231
     …..
     ….
    20180930
  

Скрипт будет:

 Step one:  find the yyyy/mm/dd in the file name (e.g. 19921231)

Step two:  copy the query

Step three: change the yyyy/mm/dd in the copied file name(s)
IF 1231 change to “ 1”0331   (e.g. 19921231 to 19930331)
IF 0331 change to 0630       (e.g. 19930331 to 19930630)
IF 0630 change to 0930       (e.g. 19930630 to 19930930)
IF 0930 change to 1231       (e.g. 19930930 to 19931231)
IF 1231 change to  1 0331    (e.g. 19931231 to 19940331)
…..
…..
…..
IF 91231 change to 00331  (e.g. 19991231 to 20000331)
….
IF 91231 change to 0031 (e.g. 20091231 to 20100331) 

Step four: print new code block after UNION ALL 
Step five: repeat step three
Step six: repeat step four
  

Вводом будет один финансовый квартал (см. Приведенный Выше блок кода), а на выходе этот блок кода повторяется более 100 раз, причем в каждом имени файла изменяется только гггг / мм / дд. Каждый блок кода будет объединен с ОБЪЕДИНЕНИЕМ ALL:

 SELECT 
  (SELECT
      PCR.repdte
      FROM 
      All_Reports_19921231_Performance_and_Condition_Ratios as 
      PCR) AS Quarter,
  (SELECT 
      Round(AVG(PCR.lnlsdepr))
      FROM 
      All_Reports_19921231_Performance_and_Condition_Ratios as 
      PCR) AS NetLoansAndLeasesToDeposits,
 (SELECT sum(CAST(LD.IDdepsam as int))
      FROM 
     'All_Reports_19921231_Deposits_Based_on_the_Dollars250,000
      _Reporting_Threshold' AS LD) AS 
      DepositAccountsWith$LessThan$250k
UNION ALL
SELECT 
  (SELECT
      PCR.repdte
      FROM 
      All_Reports_19930330_Performance_and_Condition_Ratios as 
      PCR) AS Quarter,
   (SELECT 
     Round(AVG(PCR.lnlsdepr))
     FROM All_Reports_19930330_Performance_and_Condition_Ratios 
     as PCR) AS NetLoansAndLeasesToDeposits,
   (SELECT sum(CAST(LD.IDdepsam as int))
     FROM 
    'All_Reports_19930330_Deposits_Based_on_the_Dollars250,000
     _Reporting_Threshold' AS LD) AS 
     DepositAccountsWith$LessThan$250k
  

Шаг первый — написать генератор, который выдает строки дат с шагом в три месяца от заданной строки даты до настоящего времени. Мы можем сохранить начальную дату в datetime объекте, используя это для генерации новых строк даты на каждом шаге, и не допускать, чтобы даты перемещались за пределы настоящего. Затем мы можем использовать calendar.monthrange для поиска последних дней заданных месяцев.

 from datetime import datetime
from calendar import monthrange

def dates_from(start):
    date  = datetime.strptime(start, "%Y%m%d")
    today = datetime.today()
    while date < today:
        yield date.strftime("%Y%m%d")
        month = date.month   3
        year = date.year
        if month > 12:
            year  = 1
            month -= 12
        _, day = monthrange(year, month)
        date = datetime(year, month, day)
  

Затем мы можем использовать форматирование строки, чтобы ввести это значение в строку шаблона

 sql_template = """
SELECT 
  (SELECT
      PCR.repdte
      FROM 
      All_Reports_{0}_Performance_and_Condition_Ratios as 
      PCR) AS Quarter,
  (SELECT 
      Round(AVG(PCR.lnlsdepr))
      FROM 
      All_Reports_{0}_Performance_and_Condition_Ratios as 
      PCR) AS NetLoansAndLeasesToDeposits,
  (SELECT sum(CAST(LD.IDdepsam as int))
      FROM 
     'All_Reports_{0}_Deposits_Based_on_the_Dollars250,000
     _Reporting_Threshold' AS LD) AS 
     DepositAccountsWith$LessThan$250k"""

queries = map(sql_template.format, dates_from("19921231"))
output_string = "nUNION ALLn".join(queries)