Supongamos que tengo la siguiente entrada de muestra:

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))

Donde score es el número entre 0 y 5. ¿Cómo puedo generar un informe que muestre los nombres y el número correspondiente de estrellas?

6
Mosha Pasumansky 23 feb. 2018 a las 19:02

5 respuestas

La mejor respuesta

Podemos construir la calificación de estrellas como una cadena utilizando dos caracteres Unicode:

★ - Unicode code point 9733 
☆ - Unicode code point 9734

Podemos usar CODE_POINTS_TO_STRING función para construir las estrellas, y {{X1 }} función para producir el número correcto de estrellas

En conjunto, la solución para la entrada de muestra será:

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

Producirá el siguiente resultado:

name    score
A       ★★☆☆☆
B       ☆☆☆☆☆
C       ★★★★★
D       ★☆☆☆☆
12
Mosha Pasumansky 23 feb. 2018 a las 16:02

Agregar una opción más genérica para generar informes de series temporales / minigráficos

#standardSQL
CREATE TEMP FUNCTION sparklines(arr ARRAY<INT64>) AS ((
  SELECT STRING_AGG(CODE_POINTS_TO_STRING([code]), '') 
  FROM UNNEST(arr) el, 
  UNNEST([(SELECT MAX(el) FROM UNNEST(arr) el)]) mx, 
  UNNEST([(SELECT MIN(el) FROM UNNEST(arr) el)]) mn
  JOIN UNNEST([9602, 9603, 9605, 9606, 9607]) code WITH OFFSET pos
  ON pos = CAST(IF(mx = mn, 1, (el - mn) / (mx - mn)) * 4 AS INT64) 
)); 
WITH series AS (
  SELECT 1 id, [3453564, 5343333, 2876345, 3465234] arr UNION ALL
  SELECT 2, [5743231, 3276438, 1645738, 2453657] UNION ALL
  SELECT 3, [1,2,3,4,5,6,7,8,9,0] UNION ALL
  SELECT 4, [3245876, 2342879, 5876324, 7342564]  
)  
SELECT 
  id, TO_JSON_STRING(arr) arr, sparklines(arr) sparklines 
FROM series 

Con resultado como a continuación

Row id  arr                                 sparklines   
1   1   [3453564,5343333,2876345,3465234]   ▃▇▂▃     
2   2   [5743231,3276438,1645738,2453657]   ▇▅▂▃     
3   3   [1,2,3,4,5,6,7,8,9,0]               ▂▃▃▅▅▆▆▇▇▂   
4   4   [3245876,2342879,5876324,7342564]   ▃▂▆▇       

Agregar la versión de Mosha (tomada de sus comentarios a continuación)

#standardSQL
CREATE TEMP FUNCTION barchart(v ARRAY<FLOAT64>, MIN FLOAT64, MAX FLOAT64) AS ( 
  IF(
    MIN = MAX, 
    REPEAT(CODE_POINTS_TO_STRING([9603]), ARRAY_LENGTH(v)), 
    (
    SELECT STRING_AGG(CODE_POINTS_TO_STRING([9601 + CAST(ROUND(y) AS INT64)]), '') 
    FROM ( 
      SELECT SAFE_DIVIDE(e-min, MAX - MIN) * 7 y 
      FROM UNNEST(v) e)
    )
  )
); 
CREATE TEMP FUNCTION vbar(v ARRAY<FLOAT64>) AS ( 
  barchart(v, (SELECT MIN(a) FROM UNNEST(v) a), (SELECT MAX(a) FROM UNNEST(v) a)) 
);
WITH numbers AS (
  SELECT 1 id, [3453564., 5343333., 2876345., 3465234.] arr UNION ALL
  SELECT 2, [5743231., 3276438., 1645738., 2453657.] UNION ALL
  SELECT 3, [1.,2,3,4,5,6,7,8,9,0] UNION ALL
  SELECT 4, [3245876., 2342879, 5876324, 7342564]  
)  
SELECT 
  id, TO_JSON_STRING(arr) arr, vbar(arr) sparklines 
FROM numbers  

Si se aplica a los mismos datos ficticios que las versiones anteriores, produce a continuación

Row id  arr                                 sparklines   
1   1   [3453564,5343333,2876345,3465234]   ▃█▁▃     
2   2   [5743231,3276438,1645738,2453657]   █▄▁▂     
3   3   [1,2,3,4,5,6,7,8,9,0]               ▂▃▃▄▅▆▆▇█▁   
4   4   [3245876,2342879,5876324,7342564]   ▂▁▆█      
3
Mikhail Berlyant 28 feb. 2018 a las 00:20

Ajustar el gráfico de barras verticales en un solo carácter es un desafío porque solo podemos usar 8 alturas diferentes. Pero los gráficos de barras horizontales no tienen esta limitación, podemos escalar el gráfico horizontal por longitud arbitraria. El siguiente ejemplo usa 30, y muestra el número de nacimientos por día de la semana como un gráfico de barras horizontales. Los datos se basan en un conjunto de datos público:

create temp function hbar(value int64, max int64) as (
  repeat('█', cast(30 * value / max as int64))
);
select 
  ['sunday', 'monday', 'tuesday', 'wednesday',
   'thursday', 'friday', 'saturday'][ordinal(wday)] wday, bar from (
select wday, hbar(count(*), max(count(*)) over()) bar
from `bigquery-public-data.samples.natality`
where wday is not null
group by 1
order by 1 asc)

Resultados en

wday      bar
---------------------------------------------
sunday    ███████████████████
monday    ███████████████████████████
tuesday   ██████████████████████████████
wednesday ██████████████████████████████
thursday  █████████████████████████████
friday    █████████████████████████████
saturday  █████████████████████
0
Mosha Pasumansky 15 mar. 2018 a las 17:25

Más locura aquí 😊
Totalmente inútil, pero divertido de jugar

Aplicando todas las diferentes opciones presentadas en esta publicación para el procesamiento y el dibujo de imágenes (utilizando imágenes de perfil de los que contribuyen en esta publicación) + algunas novedades





Primer y segundo resultado (para la imagen de Felipe) producidos utilizando el enfoque Color Gradient de Felipe con diferentes opciones de escala

3er resultado - usando el enfoque de Shaded Gradient de Felipe

4to resultado - usando el enfoque de Mikhail (mío) / Mosha Spark-line

Finalmente, quinto y sexto resultados, utilizando conjuntos de caracteres ASCII que representan ASCII Shades of Gray, respectivamente:
Conjunto corto - ".:-=+*#%@"
Conjunto completo (largo) - "$@B%8&WM#*oahkbdpqwmZO0QLCJUYXzcvunxrjft/\|()1{}[]?-_+~<>i!lI;:,"^``'."

El código es trivial y literalmente igual que en las respuestas respectivas: la única diferencia es que los datos utilizados en los ejercicios anteriores son los datos de píxeles de la imagen que simplemente se obtienen usando HTML canvas getImageData() Method, obviamente fuera de BigQuery, con una simple página html

Opciones para enloquecer aquí y divertirse jugando con la transformación / procesamiento de imágenes, ¡ilimitado! pero probablemente inútil fuera del alcance del aprendizaje 😜

2
Mikhail Berlyant 18 mar. 2018 a las 00:48

Mi entrada tiene un degradado de color, porque los minigráficos solo se ven bien con ciertas fuentes, y esa no es una fuente que usa la interfaz de usuario web de BigQuery.

Durante un día, cuando Stack Overflow es el más activo por etiqueta:

#standardSQL
CREATE TEMP FUNCTION barchart(v ARRAY<FLOAT64>, mm STRUCT<min FLOAT64, max FLOAT64>) AS ((
    SELECT STRING_AGG(SUBSTR('🏿🏾🏽🏼🏻', 1+CAST(ROUND(y) AS INT64), 1), '') 
    FROM (SELECT IFNULL(SAFE_DIVIDE((e-mm.min),(mm.max-mm.min))*4, 0) y FROM UNNEST(v) e))); 
CREATE TEMP FUNCTION vbar(v ARRAY<FLOAT64>) AS ( 
  barchart(v, (SELECT AS STRUCT MIN(a), MAX(a) FROM UNNEST(v) a)) 
);


WITH top_tags AS (
 (SELECT x.value FROM (SELECT APPROX_TOP_COUNT(tag, 24) x FROM `bigquery-public-data.stackoverflow.posts_questions`, UNNEST(SPLIT(tags,'|')) tag WHERE EXTRACT(YEAR FROM creation_date)>=2016), UNNEST(x) x)
)

SELECT tag, vbar(ARRAY_AGG(1.0*hhh.count ORDER BY hhh.value)) gradient, SUM(hhh.count)  c
FROM (
  SELECT tag, APPROX_TOP_COUNT(EXTRACT(HOUR FROM creation_date), 24) h_h
  FROM `bigquery-public-data.stackoverflow.posts_questions`, UNNEST(SPLIT(tags,'|')) tag
  WHERE tag IN (SELECT * FROM top_tags) AND EXTRACT(YEAR FROM creation_date)>=2016
  GROUP BY 1
), UNNEST(h_h) hhh
GROUP BY tag
ORDER BY STRPOS(gradient, '🏼')



Row gradient                                                c       tag  
1   🏿🏿🏿🏿🏾🏽🏼🏼🏼🏻🏻🏻🏻🏼🏼🏼🏼🏽🏽🏽🏽🏾🏾🏿    317538  android  
2   🏿🏿🏿🏿🏾🏽🏼🏼🏼🏻🏻🏻🏻🏻🏻🏻🏼🏼🏽🏽🏽🏾🏾🏿    59445   asp.net  
3   🏿🏿🏿🏿🏾🏽🏼🏼🏼🏻🏻🏻🏼🏼🏼🏼🏽🏽🏽🏽🏾🏾🏾🏿    159134  ios  
4   🏿🏿🏿🏿🏾🏽🏼🏼🏼🏻🏻🏻🏻🏻🏻🏼🏼🏽🏽🏽🏽🏾🏾🏿    111988  angularjs    
5   🏿🏿🏿🏿🏾🏾🏽🏼🏼🏻🏻🏻🏻🏻🏻🏼🏼🏼🏽🏽🏽🏽🏾🏿    212843  jquery   
6   🏿🏿🏿🏾🏾🏾🏽🏼🏼🏻🏻🏻🏻🏻🏻🏻🏼🏼🏼🏽🏽🏽🏾🏿    138143  mysql    
7   🏿🏿🏿🏿🏿🏾🏽🏼🏼🏻🏻🏻🏼🏻🏻🏻🏻🏼🏼🏼🏼🏽🏾🏾    107586  swift    
8   🏿🏿🏿🏿🏾🏾🏽🏼🏼🏻🏻🏻🏼🏻🏼🏼🏼🏽🏽🏽🏽🏾🏾🏿    318294  php  
9   🏿🏿🏿🏿🏾🏾🏽🏼🏼🏻🏻🏻🏻🏻🏻🏻🏼🏼🏼🏽🏽🏽🏾🏾    84723   json     
10  🏿🏿🏿🏿🏿🏾🏽🏼🏼🏻🏻🏻🏻🏻🏻🏻🏼🏼🏼🏼🏽🏽🏾🏾    233100  html     
11  🏿🏿🏿🏿🏿🏾🏽🏼🏼🏻🏻🏻🏻🏻🏻🏻🏼🏼🏼🏽🏽🏽🏾🏿    390245  java     
12  🏿🏿🏿🏿🏿🏾🏽🏽🏼🏻🏻🏼🏻🏻🏻🏻🏼🏽🏽🏽🏽🏽🏾🏿    83787   angular  
13  🏿🏿🏿🏿🏾🏾🏽🏽🏼🏼🏼🏼🏼🏻🏻🏻🏼🏼🏽🏽🏽🏽🏾🏿    70150   sql-server   
14  🏿🏿🏿🏿🏿🏾🏽🏽🏼🏻🏻🏻🏻🏻🏻🏻🏼🏼🏼🏼🏽🏽🏾🏾    534663  javascript   
15  🏿🏿🏿🏿🏿🏾🏽🏽🏼🏻🏻🏼🏼🏻🏻🏻🏼🏼🏽🏽🏽🏾🏾🏿    291541  c#   
16  🏿🏿🏿🏿🏿🏿🏾🏾🏽🏼🏼🏽🏼🏼🏻🏻🏻🏻🏻🏼🏼🏽🏽🏾    65668   c    
17  🏿🏿🏿🏿🏿🏾🏽🏽🏽🏼🏼🏼🏼🏻🏻🏻🏼🏼🏼🏼🏽🏽🏾🏿    111792  sql  
18  🏿🏿🏿🏿🏿🏾🏾🏽🏽🏼🏻🏼🏼🏻🏻🏻🏻🏼🏼🏼🏼🏽🏾🏾    158999  css  
19  🏿🏿🏿🏿🏿🏿🏾🏽🏽🏼🏼🏼🏼🏻🏻🏻🏻🏼🏼🏼🏼🏽🏽🏾    88146   arrays   
20  🏿🏿🏿🏿🏿🏿🏾🏾🏽🏼🏼🏽🏼🏼🏻🏻🏻🏼🏼🏼🏼🏼🏽🏾    61840   ruby-on-rails    
21  🏿🏿🏿🏿🏿🏿🏾🏾🏽🏼🏼🏼🏼🏻🏻🏻🏼🏼🏼🏼🏼🏽🏾🏾    136265  c++  
22  🏿🏿🏿🏿🏿🏾🏽🏽🏽🏻🏻🏼🏼🏻🏻🏻🏻🏼🏼🏼🏽🏽🏾🏾    104218  node.js  
23  🏿🏿🏿🏿🏿🏿🏿🏾🏾🏽🏽🏽🏼🏼🏻🏻🏻🏼🏼🏼🏼🏽🏾🏾    360396  python   
24  🏿🏿🏿🏿🏿🏿🏿🏾🏾🏽🏽🏽🏽🏼🏻🏻🏻🏼🏼🏼🏼🏽🏾🏾    98690   r   

enter image description here

Y un degradado sombreado más compacto, pero con solo 3 valores:

#standardSQL
CREATE TEMP FUNCTION barchart(v ARRAY<FLOAT64>, mm STRUCT<min FLOAT64, max FLOAT64>) AS ((
    SELECT STRING_AGG(SUBSTR('▓▒░', 1+CAST(ROUND(y) AS INT64), 1), '') 
    FROM (SELECT IFNULL(SAFE_DIVIDE((e-mm.min),(mm.max-mm.min))*2, 0) y FROM UNNEST(v) e))); 
CREATE TEMP FUNCTION vbar(v ARRAY<FLOAT64>) AS ( 
  barchart(v, (SELECT AS STRUCT MIN(a), MAX(a) FROM UNNEST(v) a)) 
);



WITH top_countries AS (
 (SELECT x.value FROM (SELECT APPROX_TOP_COUNT(country_code, 12) x FROM `ghtorrent-bq.ght_2017_09_01.users`), UNNEST(x) x)
)

SELECT vbar(ARRAY_AGG(1.0*hhh.count ORDER BY hhh.value)) gradient, SUM(hhh.count) c, country_code
FROM (
  SELECT country_code, APPROX_TOP_COUNT(EXTRACT(HOUR FROM a.created_at), 24) h_h
  FROM `githubarchive.year.2017` a
  JOIN `ghtorrent-bq.ght_2017_09_01.users` b
  ON a.actor.login=b.login
  WHERE country_code IN (SELECT * FROM top_countries) 
  AND actor.login NOT IN (SELECT value FROM (SELECT APPROX_TOP_COUNT(actor.login, 1000) x FROM `githubarchive.year.2017` WHERE type='WatchEvent'), UNNEST(x))
  AND a.type='WatchEvent'
  GROUP BY 1
), UNNEST(h_h) hhh
GROUP BY country_code 
ORDER BY STRPOS(gradient, '░')

Row gradient                    c       country_code     
1   ░░░░░░░▒▒▒▒▒▒▒▒▓▓▓▓▓▓▒▒░    204023  au   
2   ▒░░░░░░░░░▒▒▒▒▒▒▒▓▓▓▓▓▓▒    293589  jp   
3   ▓▒░░▒▒░░░░▒▒▒▒▒▒▒▓▓▓▓▓▓▓    2125724 cn   
4   ▓▓▓▒▒░░░░░░░░▒▒▒▒▒▒▒▒▓▓▓    447092  in   
5   ▓▓▓▓▓▓▒▒░░░░░░░░▒▒▒▒▒▒▒▓    381510  ru   
6   ▓▓▓▓▓▓▒▒░░░░░░░░▒▒▒▒▒▒▒▒    545906  de   
7   ▓▓▓▓▓▓▓▒░░░▒░░░░▒▒▒▒▒▒▒▒    395949  fr   
8   ▓▓▓▓▓▓▓▒▒░░░░░░░░▒▒▒▒▒▒▒    491068  gb   
9   ▒▒▒▒▓▓▓▓▓▓▓▒░░░▒░░░░░▒▒▒    419608  br   
10  ▒▒▒▒▒▒▒▓▓▓▓▓▓▒▒░░░░░░░░▒    2443381 us   
11  ▒▒▒▒▒▒▒▓▓▓▓▓▓▒▒░░░░░░░▒▒    294793  ca   

Y un código corto para minigráficos: funciona muy bien con Data Studio:

#standardSQL
CREATE TEMP FUNCTION barchart(v ARRAY<FLOAT64>, mm STRUCT<min FLOAT64, max FLOAT64>) AS ((
    SELECT STRING_AGG(SUBSTR('▁▂▃▄▅▆▇█', 1+CAST(ROUND(y) AS INT64), 1), '') 
    FROM (SELECT IFNULL(SAFE_DIVIDE((e-mm.min),(mm.max-mm.min))*7, 0) y FROM UNNEST(v) e))); 
CREATE TEMP FUNCTION vbar(v ARRAY<FLOAT64>) AS ( 
  barchart(v, (SELECT AS STRUCT MIN(a), MAX(a) FROM UNNEST(v) a)) 
);
5
Felipe Hoffa 3 may. 2018 a las 06:10