对于PowerBI数据建模问题：架构问题跨两个表进行交叉筛选感兴趣的读者，本文将会是一篇不错的选择，我们将详细介绍powerbi两个表连接关系，并为您提供关于Avro到BigTable-架构问题？、l
对于Power BI 数据建模问题：架构问题跨两个表进行交叉筛选感兴趣的读者，本文将会是一篇不错的选择，我们将详细介绍power bi 两个表连接关系，并为您提供关于Avro 到 BigTable - 架构问题？、lua – 如何使用其中一个表顺序同时对两个表进行排序？、php – 设计/架构问题：使用远程服务回滚、PostgresSql：比较两个表并获得其结果并将其与第三个表进行比较的有用信息。
本文目录一览：- Power BI 数据建模问题：架构问题跨两个表进行交叉筛选（power bi 两个表连接关系）
- Avro 到 BigTable - 架构问题？
- lua – 如何使用其中一个表顺序同时对两个表进行排序？
- php – 设计/架构问题：使用远程服务回滚
- PostgresSql：比较两个表并获得其结果并将其与第三个表进行比较
Power BI 数据建模问题：架构问题跨两个表进行交叉筛选（power bi 两个表连接关系）
如何解决Power BI 数据建模问题：架构问题跨两个表进行交叉筛选？
对数据建模非常陌生。我有一个问题，我希望 Power BI 可视化表显示来自两个不同表的数据，中间有一个关键表。 image here is the table,where the highlighted values on the right are from another table。
Here''s my schema：
retailerLookup [retailer_locality]
manual_data [retailer_locality，参与的问题，参与的评论，...，平均评分]
review_data [retailer_locality,Positive_tag,Negative_Tag,...,Sentiment]
两个表都连接到retailLookup 表，以retail_locality 为主键。如表中的图片所示，问题在于评论数据中的值没有通过。我试图使基数一对多，方向是两者，我试图使两个连接都处于活动状态，但我遇到了以下错误：
"您正在创建的关系让您可以通过以下方式过滤 RetailerLookup Region Lookup，但是一个数据表之间只有一个过滤路径 模型是允许的。停用表之间的现有关系 或改变他们的过滤方向。这种关系可以添加为 单向过滤"
解决方法
暂无找到可以解决该程序问题的有效方法，小编努力寻找整理中！
如果你已经找到好的解决方法，欢迎将解决方案带上本链接一起发送给小编。
小编邮箱:dio#foxmail.com (将#修改为@）
Avro 到 BigTable - 架构问题？
BigTable 是一个可扩展的 NoSQL 数据库服务，这意味着它是无模式的；而 Spark SQL 具有您在问题中指出的架构。
从下面的错误来看，它指的是 BigTable row key
expecting com.google.cloud.teleport.bigtable.BigtableRow,missing required field key
因此，您需要按照此 process 创建 BigTable 架构设计。
由于 HBase 也是无架构的，如果您可以灵活地使用 Spark 2.4.0
，您的用例可以通过使用 Bigtable and the HBase API 来解决至于上述用例，它看起来是一个有效的功能请求，我会将其提交给产品团队并更新您的报告编号。
lua – 如何使用其中一个表顺序同时对两个表进行排序？
例：
table1 = {2,3,1}
table2 = {a,b,c}
至
table1 = {1,2,3}
table2 = {c,a,b}
解决方法
此函数不会修改任何一个表,并返回根据第一个表排序的第二个表.您可以在第一个表中传递键的比较,就像在table.sort中一样.
local sort_relative = function(ref,t,cmp)
local n = #ref
assert(#t == n)
local r = {}
for i=1,n do r[i] = i end
if not cmp then cmp = function(a,b) return a < b end end
table.sort(r,function(a,b) return cmp(ref[a],ref[b]) end)
for i=1,n do r[i] = t[r[i]] end
return r
end
例如：
local table1 = {2,1}
local table2 = {"a","b","c"}
local sorted = sort_relative(table1,table2)
print(table.unpack(sorted))
结果是：
c a b
php – 设计/架构问题：使用远程服务回滚
例如,有以下调用的远程API：
getGroupCapacity(group)
setGroupCapacity(group, quantity)
getNumberOfItemsInGroup(group)
addItemToGroup(group, item)
deleteItemFromGroup(group, item)
任务是向某个组添加一些项目.团体有能力.
首先,我们应该检查组是否已满.如果是,请增加容量,然后添加项目.像这样的东西(例如API用SOAP公开)：
function add_item($group, $item) {
$soap = new SoapClient(...);
$capacity = $soap->getGroupCapacity($group);
$itemsInGroup = $soap->getNumberOfItemsInGroup($group);
if ($itemsInGroup == $capacity) {
$soap->setGroupCapacity($group, $capacity + 1);
}
$soap->addItemToGroup($group, $item);
}
现在如果addItemToGroup失败(项目不好)怎么办？我们需要回滚集团的能力.
现在想象一下,您必须添加10个项目进行分组,然后设置添加了一些属性的项目 – 所有这些都在一个事务中.这意味着如果它在中间某处失败,你必须将所有内容回滚到之前的状态.
没有一堆IF和意大利面条代码可能吗？任何将简化此类操作的库,框架,模式或体系结构决策(在PHP中)？
UPD：SOAP就是一个例子.解决方案应该适合任何服务,甚至是原始TCP.问题的关键是如何使用基础非事务API组织事务行为.
UPD2：我想这个问题在所有编程语言中都是一样的.所以任何答案都受到欢迎,不仅仅是PHP.
提前致谢！
解决方法:
<?PHP
//
// ObvIoUsly better if the service supports transactions but here's
// one possible solution using the Command pattern.
//
// tl;dr: Wrap all destructive API calls in IApiCommand objects and
// run them via an ApiTransaction instance. The IApiCommand object
// provides a method to roll the command back. You needn't wrap the
// non-destructive commands as there's no rolling those back anyway.
//
// There is one major outstanding issue: What do you want to do when
// an API command fails during a rollback? I've marked those areas
// with XXX.
//
// Barely tested but the idea is hopefully useful.
//
class ApiCommandFailedException extends Exception {}
class ApiCommandRollbackFailedException extends Exception {}
class ApiTransactionRollbackFailedException extends Exception {}
interface IApiCommand {
public function execute();
public function rollback();
}
// this tracks a history of executed commands and allows rollback
class ApiTransaction {
private $commandStack = array();
public function execute(IApiCommand $command) {
echo "EXECUTING " . get_class($command) . "\n";
$result = $command->execute();
$this->commandStack[] = $command;
return $result;
}
public function rollback() {
while ($command = array_pop($this->commandStack)) {
try {
echo "ROLLING BACK " . get_class($command) . "\n";
$command->rollback();
} catch (ApiCommandRollbackFailedException $rfe) {
throw new ApiTransactionRollbackFailedException();
}
}
}
}
// this groups all the api commands required to do your
// add_item function from the original post. it demonstrates
// a nested transaction.
class AddItemToGroupTransactionCommand implements IApiCommand {
private $soap;
private $group;
private $item;
private $transaction;
public function __construct($soap, $group, $item) {
$this->soap = $soap;
$this->group = $group;
$this->item = $item;
}
public function execute() {
try {
$this->transaction = new ApiTransaction();
$this->transaction->execute(new EnsureGroupAvailableSpaceCommand($this->soap, $this->group, 1));
$this->transaction->execute(new AddItemToGroupCommand($this->soap, $this->group, $this->item));
} catch (ApiCommandFailedException $ae) {
throw new ApiCommandFailedException();
}
}
public function rollback() {
try {
$this->transaction->rollback();
} catch (ApiTransactionRollbackFailedException $e) {
// XXX: determine if it's recoverable and take
// appropriate action, e.g. wait and try
// again or log the remaining undo stack
// for a human to look into it.
throw new ApiCommandRollbackFailedException();
}
}
}
// this wraps the setgroupcapacity api call and
// provides a method for rolling back
class EnsureGroupAvailableSpaceCommand implements IApiCommand {
private $soap;
private $group;
private $numItems;
private $prevIoUsCapacity;
public function __construct($soap, $group, $numItems=1) {
$this->soap = $soap;
$this->group = $group;
$this->numItems = $numItems;
}
public function execute() {
try {
$capacity = $this->soap->getGroupCapacity($this->group);
$itemsInGroup = $this->soap->getNumberOfItemsInGroup($this->group);
$availableSpace = $capacity - $itemsInGroup;
if ($availableSpace < $this->numItems) {
$newCapacity = $capacity + ($this->numItems - $availableSpace);
$this->soap->setGroupCapacity($this->group, $newCapacity);
$this->prevIoUsCapacity = $capacity;
}
} catch (SoapException $e) {
throw new ApiCommandFailedException();
}
}
public function rollback() {
try {
if (!is_null($this->prevIoUsCapacity)) {
$this->soap->setGroupCapacity($this->group, $this->prevIoUsCapacity);
}
} catch (SoapException $e) {
throw new ApiCommandRollbackFailedException();
}
}
}
// this wraps the additemtogroup soap api call
// and provides a method to roll the changes back
class AddItemToGroupCommand implements IApiCommand {
private $soap;
private $group;
private $item;
private $complete = false;
public function __construct($soap, $group, $item) {
$this->soap = $soap;
$this->group = $group;
$this->item = $item;
}
public function execute() {
try {
$this->soap->addItemToGroup($this->group, $this->item);
$this->complete = true;
} catch (SoapException $e) {
throw new ApiCommandFailedException();
}
}
public function rollback() {
try {
if ($this->complete) {
$this->soap->removeItemFromGroup($this->group, $this->item);
}
} catch (SoapException $e) {
throw new ApiCommandRollbackFailedException();
}
}
}
// a mock of your api
class SoapException extends Exception {}
class MockSoapClient {
private $items = array();
private $capacities = array();
public function addItemToGroup($group, $item) {
if ($group == "group2" && $item == "item1") throw new SoapException();
$this->items[$group][] = $item;
}
public function removeItemFromGroup($group, $item) {
foreach ($this->items[$group] as $k => $i) {
if ($item == $i) {
unset($this->items[$group][$k]);
}
}
}
public function setGroupCapacity($group, $capacity) {
$this->capacities[$group] = $capacity;
}
public function getGroupCapacity($group) {
return $this->capacities[$group];
}
public function getNumberOfItemsInGroup($group) {
return count($this->items[$group]);
}
}
// nested transaction example
// mock soap client is hardcoded to fail on the third additemtogroup attempt
// to show rollback
try {
$soap = new MockSoapClient();
$transaction = new ApiTransaction();
$transaction->execute(new AddItemToGroupTransactionCommand($soap, "group1", "item1"));
$transaction->execute(new AddItemToGroupTransactionCommand($soap, "group1", "item2"));
$transaction->execute(new AddItemToGroupTransactionCommand($soap, "group2", "item1"));
$transaction->execute(new AddItemToGroupTransactionCommand($soap, "group2", "item2"));
} catch (ApiCommandFailedException $e) {
$transaction->rollback();
// XXX: if the rollback fails, you'll need to figure out
// what you want to do depending on the nature of the failure.
// e.g. wait and try again, etc.
}
PostgresSql：比较两个表并获得其结果并将其与第三个表进行比较
表 2：trip_delivery_sales_lines
+-------+---------------------+------------+----------+------------+-------------+--------+--+| Sl no | Order_date | Partner_id | Route_id | Product_id | Product qty | amount | |+-------+---------------------+------------+----------+------------+-------------+--------+--+| 1 | 2020-08-01 04:25:35 | 34567 | 152 | 432 | 2 | 100 | || 2 | 2021-09-11 02:25:35 | 34572 | 130 | 312 | 4 | 150 | || 3 | 2020-05-10 04:25:35 | 34567 | 152 | 432 | 3 | 123 | || 4 | 2021-02-16 01:10:35 | 34572 | 130 | 432 | 5 | 123 | || 5 | 2020-02-19 01:10:35 | 34567 | 152 | 432 | 2 | 600 | || 6 | 2021-03-20 01:10:35 | 34569 | 152 | 123 | 1 | 123 | || 7 | 2021-04-23 01:10:35 | 34570 | 152 | 432 | 4 | 200 | || 8 | 2021-07-08 01:10:35 | 34567 | 152 | 432 | 3 | 32 | || 9 | 2019-06-28 01:10:35 | 34570 | 152 | 432 | 2 | 100 | || 10 | 2018-11-14 01:10:35 | 34570 | 152 | 432 | 5 | 20 | || | | | | | | | |+-------+---------------------+------------+----------+------------+-------------+--------+--+从表 2 中：我们必须在 route=152 中找到合作伙伴并找到最后 2 个销售的 product_qty 总和 [可以通过 desc order_date 选择]
。我们可以在表 3 中找到它的结果。
34567 – Serial number [ 1,8] 34570 – Serial number [ 7,9] 34569 – Serial number [6] 表 3：从表 1,2 中获得的结果
+------------+-------+| Partner_id | count |+------------+-------+| 34567 | 5 || 34569 | 1 || 34570 | 6 || | |+------------+-------+从表 4 中我们要找到上面的 partner_ids 叶数
表 4 :coupon_leaf
+------------+-------+| Partner_id | Leaf |+------------+-------+| 34567 | XYZ1 || 34569 | XYZ2 || 34569 | DDHC || 34567 | DVDV || 34570 | DVFDV || 34576 | FVFV || 34567 | FVV || | |+------------+-------+从中我们可以找到结果：
34567 – 334569-234570 -1表 5：从表 4 获得的结果
+------------+-------+| Partner_id | count |+------------+-------+| 34567 | 3 || 34569 | 2 || 34570 | 1 || | |+------------+-------+现在我们要比较表3和表5
If partner_id count [table 3] > partner_id count [table 4] Print partner_id我想要一个查询来完成所有这些操作
可以通过以下方式找到不同的partner_id：从表1
SELECT DISTINCT partner_id FROM trip_delivery_sales ts WHERE ts.route_id=''152'' GROUP BY ts.partner_id答案1
小编典典这回答了问题的原始版本。
您似乎想在汇总表2和表3之后比较总计。我不知道这table1是为了什么。它似乎没有任何作用。
所以：
select *from (select partner_id, sum(quantity) as sum_quantity from (select tdsl.*, row_number() over (partition by t2.partner_id order by order_date) as seqnum from trip_delivery_sales_lines tdsl ) tdsl where seqnum <= 2 group by tdsl.partner_id ) tdsl left join (select cl.partner_id, count(*) as leaf_cnt from coupon_leaf cl group by cl.partner_id ) cl on cl.partner_id = tdsl.partner_idwhere leaf_cnt is null or sum_quantity > leaf_cnt今天关于Power BI 数据建模问题：架构问题跨两个表进行交叉筛选和power bi 两个表连接关系的分享就到这里，希望大家有所收获，若想了解更多关于Avro 到 BigTable - 架构问题？、lua – 如何使用其中一个表顺序同时对两个表进行排序？、php – 设计/架构问题：使用远程服务回滚、PostgresSql：比较两个表并获得其结果并将其与第三个表进行比较等相关知识，可以在本站进行查询。
本文标签：
