presto | official home of the Presto distributed SQL query engine

with sample_data as(        
select 'video_a' as video_id, 'frame_1' as frame_id , 'walk' as verb union all 
select 'video_a', 'frame_2' , 'run'   union all
select 'video_a', 'frame_3' , 'sit'   union all
select 'video_a', 'frame_4' , 'walk'  union all
select 'video_a', 'frame_5' , 'walk'  union all
select 'video_a', 'frame_6' , 'walk'  union all
select 'video_b', 'frame_7' , 'stand' union all
select 'video_b', 'frame_8' , 'stand' union all
select 'video_b', 'frame_9' , 'run'   union all
select 'video_b', 'frame_10', 'run'   union all
select 'video_b', 'frame_11', 'sit'   union all
select 'video_b', 'frame_12', 'run'   union all
select 'video_b', 'frame_13', 'run'
)

select s.*, 
       dense_rank() over(order by video_id, rnk_frame / 6) batch_of_five
from
(
select video_id, frame_id, verb, 
       CAST(regexp_extract(frame_id,'_(\d*)$',1) AS INT) frame_number,
       dense_rank() over(partition by video_id order by CAST(regexp_extract(frame_id,'_(\d*)$',1) AS INT)) rnk_frame
  from sample_data
)s
order by video_id, frame_number;

video_id    frame_id    verb    frame_number    rnk_frame   batch_of_five
video_a     frame_1     walk    1                1           1
video_a     frame_2     run     2                2           1
video_a     frame_3     sit     3                3           1
video_a     frame_4     walk    4                4           1
video_a     frame_5     walk    5                5           1
video_a     frame_6     walk    6                6           2
video_b     frame_7     stand   7                1           3
video_b     frame_8     stand   8                2           3
video_b     frame_9     run     9                3           3
video_b     frame_10    run     10               4           3
video_b     frame_11    sit     11               5           3
video_b     frame_12    run     12               6           4
video_b     frame_13    run     13               7           4

-- sample data
WITH dataset (id, product,   market ) AS (
    values (1, 'Cabinet', 'AU,GB,FR'),
    (2, 'Chair', 'US,GB'),
    (3, 'Desk', 'BE,GB,FR,US')
) 

--query
SELECT id, product, m as market
FROM dataset
CROSS JOIN UNNEST (split(market, ',')) as t(m)

-- sample data
WITH dataset (date, value) AS (
    VALUES (date '2021-01-01' ,     10),
(date '2021-01-30' ,     20), 
(date '2021-02-10' ,     10),
(date '2021-04-01' ,     20),
(date '2021-04-02' ,     10),
(date '2021-07-10',      20)
) 

--query
SELECT avg(value)
FROM dataset
WHERE  month(date) / 3 = 1
GROUP BY month(date) / 3

-- sample data
WITH dataset (City_ID , Store_ID , latitude , longitude , radius ) AS (
    VALUES (12345   , 'store_01' , 36.1234  , 31.1234   , 3.11),
(12345   , 'store_02' , 36.5678  , 31.5678   , 2.52)
) 

--query
select city_id,
    store_id,
    geometry_union(
        transform(bingTiles_around, t->bing_tile_polygon(t))
    )
from(
        select city_id,
            store_id,
            latitude,
            longitude,
            radius,
            bing_tiles_around(latitude, longitude, 10, radius) as bingTiles_around
        from dataset
    )

RewriteEngine ON
RewriteBase /

RewriteCond %{ENV:REDIRECT_STATUS} ^$
##using THE_REQUEST variable for condition check.
RewriteCond %{THE_REQUEST} \s/([^.]*)\.php/?\s [NC]
##Performing external redirect here.
RewriteRule ^  %1? [R=301,L]

##Performing rewrite for non-existing pages.
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{DOCUMENT_ROOT}/$1.php -f
RewriteRule ^(.*)/?$ /$1.php [QSA,L]

Previous
Next

function changePage(prevOrNext) {
  var pages = document.getElementsByTagName('main')[0].children;
  for (let pageIndex = 0; pageIndex < pages.length; pageIndex++) {
    if (pages[pageIndex].style.display == "block") {
      if (prevOrNext == 1 && pageIndex < pages.length - 1) {
        pages[pageIndex].style.display = "none"
        pages[pageIndex + 1].style.display = "block"
        document.body.scrollTop = document.documentElement.scrollTop = 0;
      } else if (prevOrNext == 0 && pageIndex > 0) {
        pages[pageIndex].style.display = "none"
        pages[pageIndex - 1].style.display = "block"
        document.body.scrollTop = document.documentElement.scrollTop = 0;
      }
    }
  }
}

body {
  font-size: 18px;
  background: url("https://www.toptal.com/designers/subtlepatterns/patterns/groovepaper.png");
  font-family: 'Gelasio', serif;
}

main {
  color: black;
  font-size: 1.1rem;
  display: flex;
  flex-direction: column;
  width: 100%;
  margin-left: auto;
  margin-right: auto;
}

main div {
  width: 100%;
  padding: 6rem 5rem;
  display: none
}

h2 {
  text-align: center;
  font-size: 1.2rem;
  font-weight: normal;
  margin-bottom: 6rem;
}

h1 {
  font-family: 'Ibarra Real Nova', serif;
  text-align: center;
  font-weight: 400;
  font-size: 3rem;
  text-transform: uppercase;
  margin-bottom: 6rem;
  letter-spacing: .1rem;
}

.right-page {
  margin-top: 0;
  padding-top: 0;
}

.right-page p {
  line-height: 1.4;
  text-align: justify;
  text-justify: inter-word;
}

.right-page p:first-letter {
  font-family: 'Ibarra Real Nova', serif;
  font-size: 4.5rem;
  float: left;
  margin-top: .5rem;
  margin-right: 1rem;
  line-height: .7;
}

.left-page {
  text-align: center;
  padding-top: 4rem;
}

.left-page small {
  font-style: italic;
}

.left-page img {
  max-width: 90%;
  display: block;
  margin-left: auto;
  margin-right: auto;
}

@media screen and (min-width: 900px) {
  main {
    flex-direction: row;
    with: 100%;
    max-width: 1800px;
  }
  main div {
    width: 50%;
  }
  .left-page {
    padding-top: 14rem;
  }
  .right-page {
    padding-top: 6rem;
    max-height: 100vh;
    height: 100vh;
  }
}

  
   Old book Stories
  
  
  




  
  
    
      
      But go there, lonely,

At eventide,

And hearken, hearken

To the lisping tide.


    
    
      [ 1 ]
      Depender
      

        En cuanto a todas las cosas que existen en el mundo, unas dependen de nosotros, otras no dependen de nosotros. De nosotros dependen; nuestras opiniones, nuestros movimientos, nuestros deseos, nuestras inclinaciones, nuestras aversiones; en una palabra,
        todas nuestras acciones.
 Así, ante toda fantasía perturbadora, está presto a decir: “Tu no eres sino una
      imaginación, y en absoluto eres lo que parece”, enseguida examínala con atención y ponla a prueba, para ello sírvete de las reglas que tienes, principalmente con esta primera que es, a saber : de si la cosa que te hace penar es del número de
        aquellas que dependen de nosotros o de aquellas que no están en nuestro poder. Di sin titubear: “Esa en nada me atañe”.
      
    
  
  




  Previous
  Next

INSERT OVERWRITE TABLE table1 PARTITION(cmny_cd,order_dt)
select 
        column1,
        columnx
        ...
  from table1
  ---you can limit partitions in the WHERE

with mydata as (
select 1 id, array['chair','desk'] as myarray union all
select 2 id, array['screen','charger','computer']  union all
select 3 id, array['calendar', 'screen']
)

select id, 
       cardinality(array_intersect(myarray, array['chair','desk','charger']))>0 as contains_flag
  from mydata
 order by id

id  contains_flag
1   TRUE
2   TRUE
3   FALSE