建立資料集的常見問題 - Quick BI

本文匯總了建立資料集的常見問題。

資料集報錯問題
- 自訂SQL報錯
- 計算欄位常見報錯原因

建立資料集問題
- 使用本地檔案建立資料集時，需要注意什麼問題？
加工資料集欄位問題
資料集許可權問題
- 是否支援設定行級許可權？
資料集顯示問題
資料集關聯問題
資料集效能問題
- 資料集開啟查詢結果緩衝有什麼用？
- sql資料集查詢時間過長該如何最佳化？

自訂SQL報錯

若您的自訂SQL運行或儲存時出現報錯，請優先排查是否是以下原因。

1.自訂SQL文法不正確

Quick BI會把自訂SQL進行部分處理後下發至資料庫執行，包括：預留位置解析、limit語句限制資料行數以及增加SQL注釋。因此，若您的自訂SQL文法不正確，底層資料庫會拋出錯誤。

若您的自訂SQL報錯，建議您先把同樣的SQL語句在資料庫執行一次，以此排查是否存在SQL文法問題。您可以在“記錄”中看到歷史啟動並執行SQL語句。

2.SELECT語句之前寫了其他語句

為了避免資料量過大造成的效能問題，在自訂SQL運行和儲存時，Quick BI會在自訂SQL的最外層拼接limit 200後再下發查詢。這就要求自訂SQL必須以SELECT語句開始，面板中不支援直接寫入HINT語句。若您的SELECT語句前寫了其他語句，會導致自訂SQL報錯。

若您需要設定HINT語句，可以從上方的【設定HINT語句】入口設定。

支援設定HINT語句的資料來源列表見：資料來源功能項列表。

3.使用到的預留位置未賦預設值

Quick BI自訂SQL中支援通過寫入預留位置進行傳參，在查看報表時，可以通過查詢控制項把預留位置的值傳到底層SQL中，實現靈活的資料分析。預留位置使用情境和使用方法見文檔：預留位置

如果預留位置應用在SELECT語句中，必須設定預設值，否則會因為SQL不完整導致運行報錯。

比如以下例子中，值預留位置$val{profit_range}未賦預設值時，欄位“訂單等級”無法計算，因此自訂SQL報錯；給預留位置賦值為100後再運行SQL，資料可以正常展示出來。

另外：為避免在報表中可能會因為未賦值導致報錯，建議給SELECT語句後的預留位置賦“全域生效”的預設值，而不是“僅資料集”生效的預設值。

SELECT report_date, order_level, shipping_type,price,order_number,area,
   case when profit_amt< $val{profit_range} then'虧損' 
   when profit_amt> $val{profit_range} then'盈利'
   else '持平'
   end '訂單等級'
from company_sales_record
where $expr{report_date :report_date}
and $expr{order_level :order_level}
and $expr{order_number :order_number}

4.其他

建議規範使用自訂SQL中的注釋和別名，否則可能會因底層資料庫返回特殊欄位名、預留位置解析失敗等原因導致自訂SQL報錯。

另外：自訂SQL的末尾無需加“;”。

計算欄位常見報錯原因

計算欄位中引用的欄位為物理欄位，若需要進列欄位類型轉換，需要通過對應的轉換函式實現。手動調整欄位類型無法修改底層物理欄位的類型，可能會因為欄位類型錯誤導致報錯。
維度計算欄位不允許使用SUM、AVG等彙總函式，若需要進行彙總計算請儲存為度量。
文本類型的欄位僅支援COUNT、COUNTD彙總，不支援使用SUM、MAX等彙總函式，請先將欄位轉為數值後再進行彙總計算。

使用本地檔案建立資料集時，需要注意什麼問題？

當使用上傳的本地檔案建立資料集時，要遵從上傳的資料來源的資料庫文法，如果檔案是上傳到探索空間中，那麼在建立使用資料集時要遵從ClickHouse資料庫的文法。

上傳檔案到探索空間時，不支援使用自訂SQL建立資料集。上傳檔案到探索空間時，不支援跨源關聯建立資料集。如需跨源關聯建立資料集，可將檔案上傳至群空間支援跨源關聯的資料來源中使用。檔案上傳至資料庫資料來源時，如果使用自訂SQL建立資料集，查詢的表要使用上傳檔案後在資料庫自動產生的物理表名稱，而不是上傳的檔案名稱。上傳檔案對應的物理表名稱可以在資料來源上傳檔案清單中，點擊檔案的設定按鈕，進入到修改檔案上傳頁面，可以查看到物理表名稱資訊。

如何將Unix時間戳記類型欄位轉化為標準日期時間類型欄位？

當時間欄位以Unix時間戳記形式儲存且資料類型為文本或數值時，需要通過from_unixtime函數進行轉化。

在資料集編輯頁面，按照圖上步驟建立計算欄位。
儲存資料集，進行重新整理如下。

如何設定儀表板中展示的Null值或空值？

1.在資料集裡設定空值展示樣式。

2.在儀表板裡設定空值展示樣式。

如何通過參數SQL實現佔比的靈活運算？

目前Quick BI是不支援直接計算佔比的，您可以通過參數SQL建立資料集，實現佔比的計算。

假設目前資料表結構為：日期（date）、省份（province）、城市（city）、銷售額（order_amt）。您需要1、按城市維度匯總，計算占所屬省份銷售額的比值；2、日期可以選擇任意區間，省份、城市可以多選。

本例以通過參數SQL構建資料集模型，參考SQL如下：

select a.city,sum(fenzi)/sum(fenmu) as ratio
from
(select province,city,sum(order_amt) fenzi
 from  zhanbi_test
 where  $expr{date:date_para}
 and    $expr{province:province_para}
 and    $expr{city:city_para}
 group by province,city
)a
left  join
(select province,sum(order_amt) fenmu
 from  zhanbi_test
 where  $expr{date:date_para}
 and    $expr{province:province_para}
 and    $expr{city:city_para}
 group by province
)b on a.province=b.province

上述SQL是以city欄位彙總的樣本，您也可以為其他欄位做彙總。通過這種參數形式產生的SQL，還需要在資料集的參數設定中，將日期欄位轉換成日期類型後，就可以建立資料集，並儀表板中展示。

如何通過參數SQL實現累計計算？

目前通過配置方式，支援當年累計、曆年累計、月累計、季度累計和自訂，當資料集配置了財年時，且日期欄位為財年欄位時，日粒度支援財年累計和財季累計。具體請參見日期累計。

配置時，需要維度中選擇日期類型的day欄位才能配置，如下圖所示。

如果您需要任意選擇一段時間，從選擇的起始日期開始，之後再統計每個周期的累計，則需要通過參數SQL實現，月累計的參考語句如下：

select  a.mon_date,avg(a.order_num) order_num,sum(b.order_num) add_num
from (
select date_format(report_date,'%Y/%m') mon_date,count(distinct order_id) order_num,max(date_format(report_date,'%Y/%m')) max_mon_date
from  company_sales_record_copy
where  $expr{report_date:month_date}
group by date_format(report_date,'%Y/%m')
)a
left join(
select date_format(report_date,'%Y/%m') mon_date,count(distinct order_id) order_num
from  company_sales_record_copy
where  $expr{report_date:month_date}
group by date_format(report_date,'%Y/%m')
)b on a.max_mon_date>=b.mon_date
group by a.mon_date

此時儀表板中，只要查詢條件關聯了參數欄位，就可以通過篩選不同的月份區間，查詢各個月份從起始月份開始，每個月的累計值。

如何通過參數SQL實現輸入單個日期，可以查詢最近n天資料？

目前Quick BI展示資料的邏輯為輸入單個日期，只展示單個日期的資料；輸入日期區間，才能展示日期區間的資料。如果有特殊需求，例如需要篩選兩個圖表，其中一個圖表展示某一天資料，另一個圖表展示該天起，最近3天的資料，則可以通過參數SQL來實現。參考SQL如下：

select   report_date,area,product_type,count(distinct order_id) order_num
from   company_sales_record
where  area in ('西南','西北','華北')
and    ( $expr{dateadd(report_date,1,'dd'):date1}
or      $expr{dateadd(report_date,2,'dd'):date1}
or      $expr{dateadd(report_date,3,'dd'):date1})
group by area,product_type,report_date