104_Power Query 数据库条件查询

2020年3月1日11:42:37 评论 619 1720字阅读5分44秒

焦棚子的文章目录

请在文末下载附件

 

1、应用场景

底层数据在数据库(sql server数据库,其他数据库同理,下文不再说明。)中,Excel中有查询的字段,需要在数据库中查询相关信息;

2、举个栗子

A、数据库内容

104_Power Query 数据库条件查询创建测试表:kucunbiao

sql 代码,自己建个测试库,我测试库:kucun

 

use kucun

create table kucunbiao
(
统计日期	date
,sku		varchar(20)
,库存		int
)

insert into kucunbiao(统计日期,sku,库存)
VALUES
('2018/9/17','1001',	1)
,('2018/9/17','1002',	2)
,('2018/9/17','1003',	3)
,('2018/9/17','1004',	4)
,('2018/9/17','1005',	5)
,('2018/9/16','1001',	10)
,('2018/9/16','1002',	20)
,('2018/9/16','1003',	30)
,('2018/9/16','1004',	40)
,('2018/9/16','1005',	50)

select * from kucunbiao

 

B、Excel内容

104_Power Query 数据库条件查询
Excel内容

需要从待查询的sku中,在数据库中查询,统计日期为:2019/9/16的库存信息,结果为:查询结果。

3、上pq语句

基本思路,用Sql.Database函数,四个参数分别为:数据库ip地址,数据库名称,sql语句,timeout时长。

构建四个参数。

let
    SKU明细 = Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="SKU"]}[Content],{{"SKU", type text}})[SKU],
    //取表中的sku
    计数 = List.Count(SKU明细)-1,
    //计数用在后续参数中
    日期 = Text.From(Date.From(DateTime.LocalNow())-#duration(1,0,0,0)),
    //日期查询为2018/9/16,因为是今天所以减去一天,转换为文本用于后续文本连接。
    ip = "127.0.0.1",
    //数据IP地址为本机,注意mysql等需要端口号,sql server 用的默认端口1433
    数据库 = "kucun",
    //测试数据库:kucun
    sql_0 = "select SKU  into #SKULS #(lf)from kucunbiao #(lf)where 1=2 #(lf) #(lf) ",
    //创建临时表
    sql_1 = Text.Combine(List.Transform({0..计数},(X)=> "insert into #SKULS (SKU) values('"& SKU明细{X} &"')"),"#(lf)")&"#(lf) #(lf)",
    //将【SKU明细】中sku写入临时表,sql_2构建完整的sql查询语句。
    sql_2 = "select A.SKU, A.库存
from
(
select SKU,sum(库存) as 库存
from kucunbiao
where 统计日期='"&日期&"' 
group by SKU
) as A
inner join 
(
select SKU  from #SKULS
) as B 
on
A.SKU=B.SKU 
drop table #SKULS",

    sql = sql_0
&sql_1
&sql_2,

    结果 = Sql.Database(ip, 数据库, [Query=sql, CommandTimeout=#duration(0, 0, 10, 0)])
in
    结果

 

分步图解

104_Power Query 数据库条件查询分步1:清洗得到sku
104_Power Query 数据库条件查询分步2:计数-1得到索引
104_Power Query 数据库条件查询
分步3:构建查询日期条件
104_Power Query 数据库条件查询
分步4:构建ip
104_Power Query 数据库条件查询
分步5:构建数据库名称
104_Power Query 数据库条件查询
分步6:构建临时表sql语句
104_Power Query 数据库条件查询
分步7:构建插入临时表数据sql语句
104_Power Query 数据库条件查询
分步8:构建内连接查询sql语句
104_Power Query 数据库条件查询
分步9:组合所有sql语句
104_Power Query 数据库条件查询
分步10:利用Sql.Database查询结果

4、说明

首次使用会有凭据信息填写。

此案例适合小量数据条件查询,大量数据还请直接使用数据库。

思维的重点就是构建,要什么给什么。

by焦棚子

焦棚子的文章目录

请点击【立即购买】或者【升级VIP】获得案例附件。

隐藏内容需要支付:¥5
立即购买 升级VIP
焦棚子
  • 本文由 发表于 2020年3月1日11:42:37
  • 除非特殊声明,本站文章均为焦棚子原创,转载请务必保留本文链接
142_Power BI之同比预测 Power BI

142_Power BI之同比预测

焦棚子的文章目录 请在文末下载附件 一、背景 最近刚好在做一个简单同比预测的模型,预测方法很简单,就是累计同比预测,把MTD展示出来。 二、数据源 1、日期表:Calendar 2、事实表1:Data...
140_Power BI&Power Pivot之降维展示同类型比较 Power BI

140_Power BI&Power Pivot之降维展示同类型比较

焦棚子的文章目录 请在文末下载附件 一、背景 最近一段时间比较忙,几乎没有时间更新网站内容,今天刚好周末,更新一个简单的需求。 上效果图: 在我们日常做对比分析的时候,经常几个相同维度(比如省份、区域...