if语句中的sql嵌套flatten；多层展平中的重键控

当前查询：

with
report as (

  select * from Report_Data
  where type = 'pnl'
  and (created_at > '2020-01-01' or updated_at > '2020-01-01')

    ),
flattened as (

    select 
        report.company_ID as "CID",
        report.type,
        report.from_date as "Report_Date",
        f.*
        --f.value:"title"

    from report,
      lateral flatten(input =>prepared:profitAndLoss:report:sections) f
      --,lateral flatten(input =>f.value)f1

)
select * from flattened where CID = 908 limit 100;

f的电流输出：

[
  {
    "rows": [
      {
        "emphasize": false,
        "name": "Product Sales",
        "values": [
          {
            "rawValue": 19889.16,
            "value": "19,889"
          },
          {
            "rawValue": 56181.94,
            "value": "56,182"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Credits from Vendors",
        "values": [
          {
            "rawValue": 38,
            "value": "38"
          },
          {
            "rawValue": 47.16,
            "value": "47"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Sales - PayPal",
        "values": [
          {
            "rawValue": 0,
            "value": "0"
          },
          {
            "rawValue": 275,
            "value": "275"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Sales Returns and Allowances",
        "values": [
          {
            "rawValue": -379.28,
            "value": "(379)"
          },
          {
            "rawValue": -941.78,
            "value": "(942)"
          }
        ]
      }
    ],
    "summary": {
      "emphasize": false,
      "name": "Total Revenue",
      "values": [
        {
          "rawValue": 19547.88,
          "value": "19,548"
        },
        {
          "rawValue": 55562.32,
          "value": "55,562"
        }
      ]
    },
    "title": "Revenue"
  },
  {
    "rows": [
      {
        "emphasize": false,
        "name": "Product Costs",
        "values": [
          {
            "rawValue": 9857.22,
            "value": "9,857"
          },
          {
            "rawValue": 31907.32,
            "value": "31,907"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Supplies",
        "values": [
          {
            "rawValue": 0,
            "value": "0"
          },
          {
            "rawValue": 109.92,
            "value": "110"
          }
        ]
      }
    ],
    "summary": {
      "emphasize": false,
      "name": "Total Cost of Sales",
      "values": [
        {
          "rawValue": 9857.22,
          "value": "9,857"
        },
        {
          "rawValue": 32017.24,
          "value": "32,017"
        }
      ]
    },
    "title": "Less Cost of Sales"
  },
  {
    "rows": [
      {
        "emphasize": true,
        "name": "Gross Profit",
        "values": [
          {
            "rawValue": 9690.66,
            "value": "9,691"
          },
          {
            "rawValue": 23545.08,
            "value": "23,545"
          }
        ]
      }
    ],
    "summary": {
      "name": "",
      "values": []
    },
    "title": ""
  },
  {
    "rows": [
      {
        "emphasize": false,
        "name": "Advertising & Marketing",
        "values": [
          {
            "rawValue": 100,
            "value": "100"
          },
          {
            "rawValue": 100,
            "value": "100"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Advertising Expenses",
        "values": [
          {
            "rawValue": 22,
            "value": "22"
          },
          {
            "rawValue": 689,
            "value": "689"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Bank Fees",
        "values": [
          {
            "rawValue": 53,
            "value": "53"
          },
          {
            "rawValue": 53,
            "value": "53"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Business Telephone and Internet",
        "values": [
          {
            "rawValue": 10.08,
            "value": "10"
          },
          {
            "rawValue": 30.24,
            "value": "30"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Charitable Contributions",
        "values": [
          {
            "rawValue": 0,
            "value": "0"
          },
          {
            "rawValue": -81.89,
            "value": "(82)"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Computer and Software Expense",
        "values": [
          {
            "rawValue": 20,
            "value": "20"
          },
          {
            "rawValue": 20,
            "value": "20"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Dues and Subscriptions",
        "values": [
          {
            "rawValue": 0.99,
            "value": "1"
          },
          {
            "rawValue": 135.99,
            "value": "136"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Fees and Licenses",
        "values": [
          {
            "rawValue": 0,
            "value": "0"
          },
          {
            "rawValue": 50,
            "value": "50"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Insurance Expense - General Liability",
        "values": [
          {
            "rawValue": 101.6,
            "value": "102"
          },
          {
            "rawValue": 304.8,
            "value": "305"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Legal & Professional",
        "values": [
          {
            "rawValue": 200,
            "value": "200"
          },
          {
            "rawValue": 600,
            "value": "600"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Merchant Account Fees",
        "values": [
          {
            "rawValue": 444.91,
            "value": "445"
          },
          {
            "rawValue": 1221.71,
            "value": "1,222"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Office Expenses",
        "values": [
          {
            "rawValue": 169.66,
            "value": "170"
          },
          {
            "rawValue": 988.42,
            "value": "988"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Payroll Processing Fee",
        "values": [
          {
            "rawValue": 0,
            "value": "0"
          },
          {
            "rawValue": 0.45,
            "value": "0"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Rent",
        "values": [
          {
            "rawValue": 1500,
            "value": "1,500"
          },
          {
            "rawValue": 6000,
            "value": "6,000"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Taxes",
        "values": [
          {
            "rawValue": 0,
            "value": "0"
          },
          {
            "rawValue": 3000,
            "value": "3,000"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Travel",
        "values": [
          {
            "rawValue": 0,
            "value": "0"
          },
          {
            "rawValue": 194.35,
            "value": "194"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Utilities",
        "values": [
          {
            "rawValue": 559.59,
            "value": "560"
          },
          {
            "rawValue": 1769.63,
            "value": "1,770"
          }
        ]
      }
    ],
    "summary": {
      "emphasize": false,
      "name": "Total Operating Expenses",
      "values": [
        {
          "rawValue": 3181.83,
          "value": "3,182"
        },
        {
          "rawValue": 15075.7,
          "value": "15,076"
        }
      ]
    },
    "title": "Operating Expenses"
  },
  {
    "rows": [
      {
        "emphasize": false,
        "name": "Operating Income / (Loss)",
        "values": [
          {
            "rawValue": 6508.83,
            "value": "6,509"
          },
          {
            "rawValue": 8469.38,
            "value": "8,469"
          }
        ]
      }
    ],
    "summary": {
      "name": "",
      "values": []
    },
    "title": ""
  },
  {
    "rows": [
      {
        "emphasize": false,
        "name": "Interest",
        "values": [
          {
            "rawValue": -85.44,
            "value": "(85)"
          },
          {
            "rawValue": -434.74,
            "value": "(435)"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Interest Income",
        "values": [
          {
            "rawValue": 0.57,
            "value": "1"
          },
          {
            "rawValue": 1.24,
            "value": "1"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Other Income",
        "values": [
          {
            "rawValue": 0,
            "value": "0"
          },
          {
            "rawValue": 100,
            "value": "100"
          }
        ]
      }
    ],
    "summary": {
      "emphasize": false,
      "name": "Total Other Income and Expense",
      "values": [
        {
          "rawValue": -84.87,
          "value": "(85)"
        },
        {
          "rawValue": -333.5,
          "value": "(334)"
        }
      ]
    },
    "title": "Other Income and Expense"
  },
  {
    "rows": [
      {
        "emphasize": true,
        "name": "Net Income  / (Loss) before Tax",
        "values": [
          {
            "rawValue": 6423.96,
            "value": "6,424"
          },
          {
            "rawValue": 8135.88,
            "value": "8,136"
          }
        ]
      }
    ],
    "summary": {
      "name": "",
      "values": []
    },
    "title": ""
  },
  {
    "rows": [
      {
        "emphasize": false,
        "name": "Net Income",
        "values": [
          {
            "rawValue": 6423.96,
            "value": "6,424"
          },
          {
            "rawValue": 8135.88,
            "value": "8,136"
          }
        ]
      }
    ],
    "summary": {
      "name": "",
      "values": []
    },
    "title": ""
  },
  {
    "rows": [
      {
        "emphasize": false,
        "name": "Total Comprehensive Income",
        "values": [
          {
            "rawValue": 6423.96,
            "value": "6,424"
          },
          {
            "rawValue": 8135.88,
            "value": "8,136"
          }
        ]
      }
    ],
    "summary": {
      "name": "",
      "values": []
    },
    "title": ""
  }
]

https://codebeautify.org/jsonviewer
在上面的链接中推荐c/p json，并选择“TreeViewer”跟随我的导航参考
问题：
雪花/asni sql：
对于数组中的每个索引，我需要计算一个检查“title”键的逻辑函数，如果“title”键的值是x（为了这个练习，让我们选择“revenue”），则返回“rows”键的每个索引的“name”和“values”。低于rows:values you 有两个值。它们需要重新键入mtd和ytd。它们始终存储为[0]：mtd和[1]：ytd。我希望这些新的键出现在我命名为“周期性”的专栏中。我知道这是雪花中所有一个棘手的扁平化问题，但我整个周末都在许多不同的迭代中挣扎，想看看是否有人能尝试一下。谢谢你的帮助！
所需输出：cid，类型，报告日期，“标题”键，rows:name as “名称”，周期性，rows:values：[0]：原始值
编辑：具有以下值的示例输出列：

CID    TYPE    REPORT_DATE    "Title" Key     rows:Name as "Name" 
       47    PNL      2020-01-01         REVENUE      "Sales - PayPal"

   periodicity       rows:values:[0]:rawValue
      "MTD"                        0

所以压平并不难。如果你一步一个脚印：

with random_json as (
select '2020-05-27'::date as report_date, parse_json('[
  {
    "rows": [
      {
        "emphasize": false,
        "name": "Product Sales",
        "values": [
          {
            "rawValue": 19889.16,
            "value": "19,889"
          },
          {
            "rawValue": 56181.94,
            "value": "56,182"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Credits from Vendors",
        "values": [
          {
            "rawValue": 38,
            "value": "38"
          },
          {
            "rawValue": 47.16,
            "value": "47"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Sales - PayPal",
        "values": [
          {
            "rawValue": 0,
            "value": "0"
          },
          {
            "rawValue": 275,
            "value": "275"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Sales Returns and Allowances",
        "values": [
          {
            "rawValue": -379.28,
            "value": "(379)"
          },
          {
            "rawValue": -941.78,
            "value": "(942)"
          }
        ]
      }
    ],
    "summary": {
      "emphasize": false,
      "name": "Total Revenue",
      "values": [
        {
          "rawValue": 19547.88,
          "value": "19,548"
        },
        {
          "rawValue": 55562.32,
          "value": "55,562"
        }
      ]
    },
    "title": "Revenue"
  },
  {
    "rows": [
      {
        "emphasize": false,
        "name": "Product Costs",
        "values": [
          {
            "rawValue": 9857.22,
            "value": "9,857"
          },
          {
            "rawValue": 31907.32,
            "value": "31,907"
          }
        ]
      },
      {
        "emphasize": false,
        "name": "Supplies",
        "values": [
          {
            "rawValue": 0,
            "value": "0"
          },
          {
            "rawValue": 109.92,
            "value": "110"
          }
        ]
      }
    ],
    "summary": {
      "emphasize": false,
      "name": "Total Cost of Sales",
      "values": [
        {
          "rawValue": 9857.22,
          "value": "9,857"
        },
        {
          "rawValue": 32017.24,
          "value": "32,017"
        }
      ]
    },
    "title": "Less Cost of Sales"
  }
]') as json
), flatten_a AS (
select
        r.report_date
        ,f.value:rows as r_rows
        ,f.value:summary as r_summary
        ,f.value:title as r_title
    from random_json r
    ,lateral flatten(input=>r.json) f
), flatten_b AS (
select a.*
   , f.value:name as rr_name
   , f.value:values as rr_values
   from flatten_a a
   ,lateral flatten(input=>a.r_rows) f
)
select b.report_date
    ,b.r_title as title_key
    ,b.rr_name as name
    ,case when f.index =0 then 'MTD' when f.index=1 then 'YTD' else 'WTF' end as periodicity
    , f.value:rawValue raw_value
   from flatten_b b
   ,lateral flatten(input=>b.rr_values) f;

给予：

REPORT_DATE    TITLE_KEY              NAME                            PERIODICITY    RAW_VALUE
2020-05-27    "Revenue"              "Product Sales"                  MTD            19889.16
2020-05-27    "Revenue"              "Product Sales"                  YTD            56181.94
2020-05-27    "Revenue"              "Credits from Vendors"           MTD            38
2020-05-27    "Revenue"              "Credits from Vendors"           YTD            47.16
2020-05-27    "Revenue"              "Sales - PayPal"                 MTD            0
2020-05-27    "Revenue"              "Sales - PayPal"                 YTD            275
2020-05-27    "Revenue"              "Sales Returns and Allowances"   MTD            -379.28
2020-05-27    "Revenue"              "Sales Returns and Allowances"   YTD            -941.78
2020-05-27    "Less Cost of Sales"   "Product Costs"                  MTD            9857.22
2020-05-27    "Less Cost of Sales"   "Product Costs"                  YTD            31907.32
2020-05-27    "Less Cost of Sales"   "Supplies"                       MTD            0
2020-05-27    "Less Cost of Sales"   "Supplies"                       YTD            109.92

如果你在第一句话中提到的只是想把上面的行作为“收入”，那么把结果也设为cte，并把左连接到你的“报告”和“收入过滤器”上。

if语句中的sql嵌套flatten；多层展平中的重键控

1条答案

相关问题

热门标签

最新问答