工业用电的分时段用时问题

cnmlgb9998 · 发表于 2024-9-27 07:11

ThxAlot 发表于 2024-9-27 05:50
没觉得拆秒会不行。。。

老师，能不能按你的思路做个我学习一下。不要用递归就行

飞天篮球猪 · 发表于 2024-9-27 08:17

ThxAlot 发表于 2024-9-27 05:50
没觉得拆秒会不行。。。

Talk is cheap, show me your code!
按你的思路来，看能不能把3年的数据跑进3秒，让我学习一下。
9楼的方法可以跑进3秒，递归也快，但受限于堆栈大小，溢出是正常的，我也有方法延迟堆栈溢出。

cnmlgb9998 · 发表于 2024-9-27 11:27

飞天篮球猪发表于 2024-9-27 08:17
Talk is cheap, show me your code!
按你的思路来，看能不能把3年的数据跑进3秒，让我学习一下。
9楼的 ...

牛逼克拉斯。函数区的超级玛莉64 和 PQ区的你，是我觉得都特牛的大神，印象深刻

ThxAlot · 发表于 2024-9-27 16:06

cnmlgb9998 发表于 2024-9-27 07:11
老师，能不能按你的思路做个我学习一下。不要用递归就行

这样写出来看上去肯定是繁琐些，但性能几乎是恒定的，不管时间间隔是1天还是10年，也不存在什么stack溢出的情况。

// DIV
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WerFhu5KOkoGBFRjVGVhAGEqxOtFKT3duBslBheoMDZDlNm8AysGE6gwNkeU2TAPJGcLkjDDMhAnVGZpgmmkCkzPFNNMUJmeOqc8cJmeJaR9UqM7I2MrUEoiUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [时段 = _t, 起止时间 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"时段", type text}, {"起止时间", type text}}),
Periods = Table.TransformColumns(
#"Changed Type",
{"起止时间", each let l=List.Transform(Text.Split(_,"~"), Time.From), dur=Number.Round(Duration.TotalMinutes(l{1}-l{0})) in List.Transform({0..dur-1}, each l{0}+#duration(0,0,_,0))}
),
Grouped = Table.Group(Periods, "时段", {"grp", each List.Combine([起止时间])}),
#"To Records" = Table.ToRecords(Grouped)
in
#"To Records"
// elec
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYzBDcAgDMRWQXkjkUtIgayCsv8aQOmvT1uW56RRpAhLTc2lUd6slx9Hp8inUL5K1K3vBlzwi45BTWAXez/4xhhuRhEL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start DT" = _t, #"End DT" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start DT", type datetime}, {"End DT", type datetime}}),
#"Added whole day" = Table.AddColumn(
#"Changed Type",
"whole",
each let
d=Duration.TotalDays(Date.From([End DT])-Date.From([Start DT]))-1
in List.Transform(DIV, each Record.TransformFields(_, {"grp", each d*List.Count(_)}))
),
#"Added Stt" = Table.AddColumn(
#"Added whole day",
"Stt",
each let
stt=Time.From([Start DT]),
dur=Number.Round(Duration.TotalMinutes(#time(23,59,59)-stt)),
l=List.Transform({0..dur-1}, each stt+#duration(0,0,_,0))
in List.Transform(DIV, each Record.TransformFields(_, {"grp", each List.Count(List.Intersect({l,_}))})), type list
),
#"Added End" = Table.AddColumn(
#"Added Stt",
"End",
each let
end=Time.From([End DT]),
dur=Number.Round(Duration.TotalMinutes(end-#time(0,0,0))),
l=List.Transform({0..dur-1}, each end-#duration(0,0,_,0))
in List.Transform(DIV, each Record.TransformFields(_, {"grp", each List.Count(List.Intersect({l,_}))})), type list
),
#"Combined Columns" = Table.CombineColumns(#"Added End", {"whole","Stt","End"}, each let t=Table.FromRecords(List.Combine(_)) in Table.Pivot(t, List.Distinct(t[时段]), "时段", "grp", List.Sum), "comb"),
#"Expanded comb" = Table.ExpandTableColumn(#"Combined Columns", "comb", {"谷", "平", "峰", "尖"})
in
#"Expanded comb"

复制代码

ThxAlot · 发表于 2024-9-27 16:33

飞天篮球猪发表于 2024-9-27 08:17
Talk is cheap, show me your code!
按你的思路来，看能不能把3年的数据跑进3秒，让我学习一下。
9楼的 ...

呵呵，杠精你好，还是会拽English的，对对对，你说的都对

我们公司这边还在用一种叫PYTHON的古老编程语言去做一些处理和计算，论效率还算可以吧，不知道能不能跑进一秒；要么就是去写DAX公式。PQ的计算都不太上得了台面，还得另外付上文档说明以供后期维护。。。

cnmlgb9998 · 发表于 2024-9-27 19:50

ThxAlot 发表于 2024-9-27 16:33
呵呵，杠精你好，还是会拽English的，对对对，你说的都对

大神：能不能用PQ，按你的思路解答一下？我想学学。谢谢！

ThxAlot · 发表于 2024-9-27 20:56

cnmlgb9998 发表于 2024-9-27 19:50
大神：能不能用PQ，按你的思路解答一下？我想学学。谢谢！

在发给你的回帖里贴了代码和附件，但是一直在审查状态。

现在试一下这个能不能直接出来

// DIV
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WerFhu5KOkoGBFRjVGVhAGEqxOtFKT3duBslBheoMDZDlNm8AysGE6gwNkeU2TAPJGcLkjDDMhAnVGZpgmmkCkzPFNNMUJmeOqc8cJmeJaR9UqM7I2MrUEoiUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [时段 = _t, 起止时间 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"时段", type text}, {"起止时间", type text}}),
Periods = Table.TransformColumns(
#"Changed Type",
{"起止时间", each let l=List.Transform(Text.Split(_,"~"), Time.From), dur=Number.Round(Duration.TotalMinutes(l{1}-l{0})) in List.Transform({0..dur-1}, each l{0}+#duration(0,0,_,0))}
),
Grouped = Table.Group(Periods, "时段", {"grp", each List.Combine([起止时间])}),
#"To Records" = Table.ToRecords(Grouped)
in
#"To Records"
// elec
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZYzBDcAgDMRWQXkjkUtIgayCsv8aQOmvT1uW56RRpAhLTc2lUd6slx9Hp8inUL5K1K3vBlzwi45BTWAXez/4xhhuRhEL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start DT" = _t, #"End DT" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start DT", type datetime}, {"End DT", type datetime}}),
#"Added whole day" = Table.AddColumn(
#"Changed Type",
"whole",
each let
d=Duration.TotalDays(Date.From([End DT])-Date.From([Start DT]))-1
in List.Transform(DIV, each Record.TransformFields(_, {"grp", each d*List.Count(_)}))
),
#"Added Stt" = Table.AddColumn(
#"Added whole day",
"Stt",
each let
stt=Time.From([Start DT]),
dur=Number.Round(Duration.TotalMinutes(#time(23,59,59)-stt)),
l=List.Transform({0..dur-1}, each stt+#duration(0,0,_,0))
in List.Transform(DIV, each Record.TransformFields(_, {"grp", each List.Count(List.Intersect({l,_}))})), type list
),
#"Added End" = Table.AddColumn(
#"Added Stt",
"End",
each let
end=Time.From([End DT]),
dur=Number.Round(Duration.TotalMinutes(end-#time(0,0,0))),
l=List.Transform({0..dur-1}, each end-#duration(0,0,_,0))
in List.Transform(DIV, each Record.TransformFields(_, {"grp", each List.Count(List.Intersect({l,_}))})), type list
),
#"Combined Columns" = Table.CombineColumns(#"Added End", {"whole","Stt","End"}, each let t=Table.FromRecords(List.Combine(_)) in Table.Pivot(t, List.Distinct(t[时段]), "时段", "grp", List.Sum), "comb"),
#"Expanded comb" = Table.ExpandTableColumn(#"Combined Columns", "comb", {"谷", "平", "峰", "尖"})
in
#"Expanded comb"

复制代码

tcdatongye · 发表于 2024-9-30 19:12

let
源 = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
tab = Excel.CurrentWorkbook(){[Name="表5"]}[Content],
k=Table.TransformColumns(tab,{"起止时间",each [
a=Text.Split(Text.Replace(_,"24","1.00"),"~"),
b=Duration.From(a{0}),
c=Duration.FromText(a{1}),
d=c-b
] }),
查询 = Table.ExpandRecordColumn(k, "起止时间", {"b", "c", "d"}),
ntol=List.Count(查询[时段]),//查询记录总数
c=List.Distinct(查询[时段]),
d=Record.FromList(List.Repeat({Duration.From(0)},List.Count(c)),c),//取查询行数，查询时间分类名称。
e=[a=0,b=Duration.From(0)] & d,//制作循环计数记录。
m=Table.ToList(源,each [
s=[a=Duration.From(Text.Split(Text.From(_{0})," "){1}), b=_{1}-_{0}],//每条记录取初始时间与首尾差值
d=List.Generate(()=>e, (x)=>x[b]<=s[b],(y)=>[
p=if y[a]=0 then Table.RowCount(Table.SelectRows(查询,each s[a]>=[b]))-1 else y[a],
b=Number.Mod(p,ntol),
j=if y[a]=0 then 查询[c]{b}-s[a] else 查询[d]{b},
k=if y[b]=s[b] then s[b] else if s[b]-y[b]<j then s[b]-y[b] else j,
n=Record.TransformFields(y,{查询[时段]{b},each _+k}) & [a=p+1,b=y[b]+k]
][n]),
f=List.Last(d)
][f] ),
extab =Table.SelectColumns(Table.FromRecords(m),c),
retab=Table.TransformColumns(extab,{},each if _>Duration.From(0) then Number.ToText(Duration.TotalMinutes(_),"f") else null)
in
retab

复制代码

tcdatongye · 发表于 2024-9-30 19:16

cnmlgb9998 发表于 2024-9-27 19:50
大神：能不能用PQ，按你的思路解答一下？我想学学。谢谢！

[color=rgb(153, 153, 153) !important][url=]帖子[/url]

搜索

ExcelHome技术论坛›论坛›Excel 技术交流与问答›Power BI›工业用电的分时段用时问题›参与/回复主题

RE: 工业用电的分时段用时问题 [[url=]修改[/url]]

cnmlgb9998 发表于 2024-9-27 19:50
大神：能不能用PQ，按你的思路解答一下？我想学学。谢谢！

天道酬勤y · 发表于 2024-10-11 18:35

我们公司只能用wps 你这个pq看着真复杂

		自动登录	找回密码
密码			免费注册

[分享] 工业用电的分时段用时问题

评分

评分

评分