如何建立“星级”以BigQuery报告(或迷你图或颜色渐变) [英] How to build "Star Rating" report in BigQuery (or sparklines, or color gradients)
问题描述
WITH评级AS(
(SELECT'A'name, 2分)UNION ALL
(SELECT'B'name,0 score)UNION ALL
(SELECT'C'name,5 score)UNION ALL
(SELECT'D'name,1 score ))
其中得分
是介于0和5.
我怎样才能生成一个显示姓名和相应星号的报表?
★ - Unicode代码点9733
☆ - Unicode代码点9734
我们可以使用 CODE_POINTS_TO_STRING
函数来建立星星, REPEAT
函数生成正确数量的星星
将样本输入的解决方案组合在一起将会是:
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $(
(SELECT'A'name, 2分)UNION ALL
(SELECT'B'name,0 score)UNION ALL
(SELECT'C'name,5 score)UNION ALL
(SELECT'D'name,1 score ))
SELECT
名称,
CONCAT(
REPEAT(CODE_POINTS_TO_STRING([9733]),分数),
REPEAT(CODE_POINTS_TO_STRING([9734] ),5分))得分
从评级
它会产生以下结果:
名字得分
A★★☆☆☆
B☆☆☆☆☆
C★★★★★
D★☆☆☆☆
Suppose I have the followng sample input:
WITH Ratings AS (
(SELECT 'A' name, 2 score) UNION ALL
(SELECT 'B' name, 0 score) UNION ALL
(SELECT 'C' name, 5 score) UNION ALL
(SELECT 'D' name, 1 score))
Where score
is number between 0 and 5.
How can I produce a report showing names and corresponding number of stars ?
We can build star rating as a string using two Unicode characters:
★ - Unicode code point 9733
☆ - Unicode code point 9734
We can use CODE_POINTS_TO_STRING
function to build the stars, and REPEAT
function to produce the right number of stars
Combined together the solution for sample input will be:
WITH Ratings AS (
(SELECT 'A' name, 2 score) UNION ALL
(SELECT 'B' name, 0 score) UNION ALL
(SELECT 'C' name, 5 score) UNION ALL
(SELECT 'D' name, 1 score))
SELECT
name,
CONCAT(
REPEAT(CODE_POINTS_TO_STRING([9733]), score),
REPEAT(CODE_POINTS_TO_STRING([9734]), 5-score)) score
FROM Ratings
It will produce the following result:
name score
A ★★☆☆☆
B ☆☆☆☆☆
C ★★★★★
D ★☆☆☆☆
这篇关于如何建立“星级”以BigQuery报告(或迷你图或颜色渐变)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!