python - SQL 会计日期、平面文件、痛苦

Question

使用sqlite3、python

我有一个蹩脚的旧文件legacy_sales:

item | year | week | mon | tue | wed | thu | fri | sat |sun 
4444   2011     29    10    0     4    15     18    25   30

我有一个很棒的新文件销售(这是上面“mon”条目的示例):

item | units |     date
4444   10       2011-03-14

我在另一个表财政中有财政年度的开始日期

year | start_date
2011   2010-08-30

有了这些信息，实现这一目标的最佳和最有效的方法是什么:

insert into sales from (insert magic here) 

不使用任何 UDF...

有这样的胡言乱语的经历吗？

Answer 1

insert into sales from (
    -- here be magic
    select  item, units, DATEADD(day, DayInYear, start_date)
    from    (
        select  item,
                year,
                (week - 1) * 7 + 0 as DayInYear,
                mon as Units
        from    legacy_sales
        union all
        select  item, year, (week - 1) * 7 + 1, tue
        from    legacy_sales
        union all
        select  item, year, (week - 1) * 7 + 2, wed
        from    legacy_sales
        union all
        select  item, year, (week - 1) * 7 + 3, thu
        from    legacy_sales
        union all
        select  item, year, (week - 1) * 7 + 4, fri
        from    legacy_sales
        union all
        select  item, year, (week - 1) * 7 + 5, sat
        from    legacy_sales
        union all
        select  item, year, (week - 1) * 7 + 6, sun
        from    legacy_sales
        ) ls
        inner join fiscal on
            fiscal.year = ls.year
)