Recopilación de sentencias SQL que trabajan con mucha dato-diversidad

postgres_recipes.sql

-- Consulta sobre el valor un miembro de primer nivel (para el caso, turnos que son sobreturnos)
select *
from appointments
where details ->> 'isOverturn' = 'true';

-- Consulta sobre el valor de un miembro de un nivel aninado (para el caso, los turnos con atención particular)
select *
from appointments
where details -> 'extra' ->> 'attentionBy' = 'particular'


-- GENERANDO TABLAS EN MEMORIA PARA PROBAR (en este caso particiones)

  -- USANDO CTE

  with cte as (
    select 1 as id, 1 as org_id
    union
    select 2 as id, 2 as org_id
    union
    select 3 as id, 1 as org_id
  )
  select *, count(*) over (partition by org_id)
  from cte

  -- USANDO FROM ( VALUES ) SIN DECLARAR TIPO DE DATO
  select id, json_each(data::json)
  from
  (
    values
    (1, '{"a":"4", "b":"5"}'::json),
    (2, '{"a":"6", "b":"7"}'::json),
    (3, '{"a":"8", "b":"9"}'::json)
  ) as q (id, data) -- forma de definir la estructura de record anonimous!

  -- USANDO FROM (VALUES)  DECLARAR TIPO DE DATO
    CREATE TYPE tabla_row AS (
    id integer,
    json_value json
  );

  select (q::tabla_row).*
  from
  (
    values
    (1, '{"a":"4", "b":"5"}'::json),
    (2, '{"a":"6", "b":"7"}'::json),
    (3, '{"a":"8", "b":"9"}'::json)
  ) as q;


select row(1,2,3)

-- Creando un arreglo de registros
select unnest(ARRAY[ROW(1,2),ROW(2,3)]) as bar

-- expandiendo un arreglo con el indice aldito (otra forma de crear un arreglo de registros)
  select items.description, -1+row_number() over () as index
  from (
    select unnest(ARRAY['Ciego',
    'Colon ascendente',
    'Ángulo hepático',
    'Colon transverso',
    'Ángulo esplénico',
    'Colon descendente',
    'Colon sigmoides',
    'Recto',
    'Ileon'])
  ) as items(description)


-- TODO: ESTUDIAR DIFERENCIA ENTRE

select *
from json_each('{"a":"foo", "b":"bar"}')

select json_each('{"a":"foo", "b":"bar"}')


-- vs esta otra funcion que como devuelve conjunto e tipos escalares (la otra devuelve un conjunto de tipos compuestos) muestran el mismo output..

select json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')


select *
from json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')

-- vs este otra función que devuelve un conjunto de tipos escalares (TABLE FUNCTIONS)

 -- EJEMPLO 1
select *
from unnest(ARRAY[1,2,3])

select unnest(ARRAY[1,2,3])

  -- EJEMPLO 2

select a.id, unnest(a.practices_ids) as practice_id
from appointments a
where a.id in (716786, 716910)


select a.id, practice_id
from appointments a,  unnest(a.practices_ids) as practice_id
where a.id in (716786, 716910)

-- CLIPBOARD



with cte as (
  select '{"pepe":1, "popo":2}'::json as json_value
)
select (temporal).*
FROM (
  SELECT json_each(json_value).'key'
  FROM cte
) as temporal(key,value)
WHERE 'key' <> 'pepe'


with cte as (
  select '{"pepe":1, "popo":2}'::json as json_value
)
SELECT json_each(json_value)
FROM cte


-- USANDO ARRAYS NATIVOS


select *
from appointments
where id = ANY (ARRAY[114991,114990])

-- SI queremos consultar sobre la existencia de un valor en un "arreglo json"

-- (A)
SELECT id, json_array_elements(posologies) as posologies,
FROM drug_indications

  -- EXPLORANDO ACTIVIDAD

  select kind,to_timestamp(created)::timestamp with time zone at time zone 'America/Argentina/Buenos_Aires'
  from activity_log
  where kind ilike 'consumptions.%' and org_id = 73
  order by id desc;


  -- USANDO (A)
	SELECT *
	FROM (
	    SELECT id,json_array_elements(posologies) AS posologies
	    FROM drug_indications
	) t
	WHERE t.posologies -> 'method' ->> 'name' = 'daily_fixed'

  -- USANDO (A) EJEMPLO DOS (Para buscar contra determinados valores dentro de un arreglo json)
select *
from practices_specifications ps
where ARRAY(SELECT json_array_elements_text(ps.details -> 'encounterModalities' )) @> '{"ftf","vr"}'::text[]


-- Si queremos saber sobre la existencia (o no) de valores en un campo json

select *
from appointments
where json_typeof(details -> 'booking') is  null

-- EJEMPLO Para buscar como esta seteado un in campo anidado en un json
select distinct id,  (extra_info -> 'customFields' -> 'HC')::jsonb
from patients
where json_typeof(extra_info -> 'customFields' -> 'HC') = 'array' (O is null)


-- MODIFICAR ATRIBUTO DE PRIMER NIVEL json_object_set_key (VER https://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype)

  update appointments
set details = json_object_set_key(details,'stateTimes',('{"recepcionado":'|| begins::TEXT || '}')::json)
where state in ('recepcionado','atendiendo','atendido') and json_typeof(details -> 'stateTimes') is null

-- ARREGLOS DE JSON

-- Funciones para manipular un arreglo
create function jsonb_array_append(j jsonb, e text)
returns jsonb language sql immutable
as $$
    select array_to_json(array_append(array(select * from jsonb_array_elements_text(j)), e))::jsonb
$$;

create function jsonb_array_remove(j jsonb, e text)
returns jsonb language sql immutable
as $$
    select array_to_json(array_remove(array(select * from jsonb_array_elements_text(j)), e))::jsonb
$$;

create function jsonb_array_replace(j jsonb, e1 text, e2 text)
returns jsonb language sql immutable
as $$
    select array_to_json(array_replace(array(select * from jsonb_array_elements_text(j)), e1, e2))::jsonb
$$;

-- Ejemplo: Agregar un rol a deshabilitar en una organización

select json_object_set_key(o.preferences,'disabledRoles', jsonb_array_append((o.preferences ->> 'disabledRoles')::jsonb, 'cryo_access'))
from organizations o
where o.id = 1


-- BUSCAR dentro de un arreglo de JSON

-- Dentro de un arreglo que tiene objetos con un campo númerico contra el cual comparo
select *
from sessions s
where <id_centro> in (
    select (json_array_elements(s.pr
      ofile -> 'centers') ->> 'id')::int as id
)


-- Dentro de un arreglo que tiene números direcmaente https://stackoverflow.com/a/20236634/903998
select *
from fertility_treatments ft
where 3 in (
  select  (json_array_elements(procedures -> 'ids'))::text::int as ids -- esta conversión aparntemente es necesario...
)

-- OTRO EJEMPLO, buscando dentro de un arreglo de números de json un valor determinado

-- (A)
    SELECT count(*)
    FROM practices_specifications ps
    WHERE $1 IN (
        SELECT (json_array_elements(ps.details -> 'a_json_int_array')::text)::int AS id
    )

-- (B)
    SELECT count(*)
    FROM (
        SELECT id as practice_specification_id,json_array_elements((details ->> 'a_json_int_array')::json) AS preference_tags_id
        FROM practices_specifications
    ) t
    where t.preference_tags_id::text::int = $1

-- Ejemplo para verificar que un valor dentro del miembro de un objeto dentro de un arreglo json corresponda con un id valido en otra tabla (para verificar integridad referencial!)

-- FORMA QUE FUNCIONA
  select *
  from services_sessions_availabilities_lapses l
  where l.tags is not null and NOT (
     ARRAY( select (jsonb_array_elements(l.tags) ->> 'tagId')::int) <@  ARRAY (select t.id from preference_tags t)
  )

-- FORMAS QUE AUN NO FUNCIONAN...
-- SIN JSONB
  select *
  from services_sessions_availabilities_lapses l
  where l.tags is not null and not exists (
    select true
    from preference_tags t
    where t.id in (
      select (jsonb_array_elements(l.tags) ->> 'tagId')::int
    )
  )

-- CON JSONB
  select *
  from services_sessions_availabilities_lapses l
  where l.tags is not null and not exists (
    select true
    from preference_tags t
    where l.tags @> ('[{"tagId":'|| t.id ||'}]')::jsonb
  )



-- EJEMPLO usando arreglo de JSON: INSERTAR ROLES FALTANTES PARA UN ADMIN en un centro(id=<ID_CENTER> de una organización=(id=<ORG_ID>)
INSERT INTO session_roles (session_id, role_id, center_id)
SELECT (select id from sessions where email = 'admin@ruca'), roles.id, <ID_CENTER>
FROM
  (select *
  from roles r
  where r.code not in (
    select json_array_elements_text((preferences ->> 'disabledRoles')::json)
    from organizations o
    where id = <ORG_ID>
  ) and r.id not in (
    select role_id
    from session_roles
    where session_id = (select id from sessions where email = 'admin@ruca')
  )) as roles

-- INSERTAR PERMISOS PARA ACCEDER A BACKOFICE CON USUARIO ADMIN
BEGIN;

ALTER TABLE session_roles DROP CONSTRAINT IF EXISTS backoffice_access_only_for_stage;

INSERT INTO session_roles (session_id, role_id, center_id)
SELECT (select id from sessions where email = 'admin@invitro') as session_id,
       (select id from roles where code =  'backoffice_access') as role_id,
        c.id as center_id
from centers c
inner join organizations o on o.name = 'invitro' and c.org_id = c.id;

COMMIT;


-- Busca el state con mayor begins que sea superado por el timestamp
select *
from deals d, json_array_elements(d.states) as state
WHERE id = 21 and (state->>'begins')::int <= $TIMESTAMP
ORDER BY (state->>'begins')::int DESC
LIMIT 1;

-- Busca el state que empiezen a partir de un timestamp que además esten activos. El order by con limit 1 selecciona el el state que empieze más tarde, haciendolo el anterior más proximo al timestamp.
-- No sirve para determinar si el deal esta activo a la fecha $TIMESTAMP, pues si hay otro state no activo con begins mayor a _ese último_ "activo" y menor al $TIMESTAMP (es decir que esté entre esos dos valores), la misma clausula del order by va a hacer que se seleccione el state no activo.
-- Entonces: Habria que pedir que no haya otro por encima de _este último_ activo que sea anterior al $TIMESTAMP. Ver *1
select *
from deals d, json_array_elements(d.states) as lastActiveState
WHERE id = 21 and (lastActiveState->>'begins')::int <=  $TIMESTAMP and (lastActiveState->>'code') = 'active'
ORDER BY (lastActiveState->>'begins')::int desc
LIMIT 1;

-- *1 De esta forma pedimos que no exista otro estado distinto a "activo" que supere al último activo encontrado y que sea superado por el $TIMESTAMP tambien!
-- Si sacmos la condicón de que sea <= timestamp en la inner query, entonces estarpiamos pidiendo que no exista al menos un estado no activo por delante del último activo (deja de importar el $TIMESTAMP)
select *
from deals d, json_array_elements(d.states) as lastActiveState
WHERE id = 21 and (lastActiveState->>'begins')::int <= $TIMESTAMP and (lastActiveState->>'code') = 'active'
  and not exists (
    select *
    from deals d, json_array_elements(d.states) as lastState
    WHERE id = 21 and (lastState->>'begins')::int <= $TIMESTAMP and (lastState->>'code') <> 'active' and (lastState->>'begins')::int >= (lastActiveState->>'begins')::int
  )
ORDER BY (lastActiveState->>'begins')::int desc
LIMIT 1;

Ejemplo:
[
  {
    "begins":0,
    "code":"active"
  },
  {
    "begins":50,
    "code":"inactive"
  },
  {
    "begins":100,
    "code":"active"
  },
  {
    "begins":500,
    "code":"inactive"
  },
  {
    "begins":1000,
    "code":"active"
  }
]

Si busco entre 0 y 49 , el select devuelve un deal.
Si buesno entre 50 y 99, el selecte devuelve nada.
Si busco entre 100 y 499, el select devuelve un deal.
Si buesno entre 500 y 999, el selecte devuelve nada.
Si busco entre 1000 y >1000, el select devuelve un deal.



-- pasar dE UNIX TIMESTAMP A UNA FECHA LEGIBLE
select state,  TIMESTAMP 'epoch' + (state->>'begins')::int * INTERVAL '1 second'
from deals d, json_array_elements(d.states) as state
WHERE d.id = 1
ORDER BY (state->>'begins') DESC


-- Pasando un arreglo a string atomico
select id,state, string_agg(item -> 'product' ->> 'name', ', ')
from appointments, json_array_elements(details -> 'extra' -> 'products') item
where id = 57712
group by id

-- Filtrando...
select id, begins, state, string_agg(item -> 'product' ->> 'name', ', ') as products_names
from appointments, json_array_elements(details -> 'extra' -> 'products') item
where patient_id = 20616
group by id
having string_agg(item -> 'product' ->> 'name', ', ') ilike '%inicio%'
order by begins;

-- TRABAJANDO CON OBJETOS JSON

select instructions.key, instructions.value -> 0 -- obtiene la clave y el primer elemento de valor (que es un arreglo)
from appointments, json_each(instructions_by_practice) as instructions
where id = 1 AND json_array_length(instructions.value) > 1

-- el json_each genera un tabla on the fly que tiene dos columnas "key" y "value"

-- Decorando un poco las cosas podemos hacer que el begins se vea en formato humano
select id, TIMESTAMP 'epoch' + (begins)::int * INTERVAL '1 second' as date,state,  to_timestamp(begins)::date, string_agg(item -> 'product' ->> 'name', ', ') as products_names
from appointments, json_array_elements(details -> 'extra' -> 'products') item
where patient_id = 20616
group by id
having string_agg(item -> 'product' ->> 'name', ', ') ilike '%inicio%'
order by date;

-- JUGANDO CON unix timestamps para averiguar fechas y otras cosas...
select (extract(epoch FROM now()) - birthdate/1000) / 86400 as days_of_life, EXTRACT(year FROM  age(to_timestamp(p.birthdate/1000)))*12 + EXTRACT(month FROM  age(to_timestamp(p.birthdate/1000))) as months_of_life, age(to_timestamp(birthdate/1000)), date_part('days',age(to_timestamp(birthdate/1000))), (birthdate - extract(epoch FROM now())) / (86400) / 364, extract (days from age(to_timestamp(birthdate/1000)))
from people p
where sid = 12



-- Uso de funciones para modificar jsons
-- Notar que hace falta usar el ::json sino no entiende el tipo de dato, vale para ambos ejemplos
select json_object_set_key('{}','path','"value"'::json)
select json_object_set_path('{}',ARRAY['path','subpath'],'"value"'::json)

-- EJEMPLO DE USO: Actualizando el HC de un paciente
UPDATE patients SET extra_info = json_object_set_path(extra_info, ARRAY['customFields','HC'], '"39089,5889"'::json) WHERE id = 160275;



-- ROLES (MOVER A OTRO LADO PAES)
select *
from session_roles
where role_id = (select id from roles where code = 'ehr_disabled_autologoff')
and center_id in (select id from centers where org_id = (select id from organizations where name = 'orionis'))

select id
from sessions s
where s.org_id = (select id from organizations where name = 'orionis')
and not exists (
  select true
  from session_roles sr
  where sr.role_id = (select id from roles where code = 'ehr_disabled_autologoff')
  and sr.center_id in (select id from centers where org_id = (select id from organizations where name = 'orionis'))
  and sr.session_id = s.id
)

select *
from session_roles sr
where sr.session_id = 77 and sr.role_id = (select id from roles where code = 'ehr_disabled_autologoff')


-- AGREGAR CHECKS Y RESTRICCIONES (CON EJEMPLOS CONCRETOS)

-- PARA INSERCIONES Y UPDATES CON UN CHECK BASTA

-- Returns true if the event with a given id (sole argument) points to a consumption_event row representing a 'cancelation'
CREATE OR REPLACE FUNCTION consumptions_events_is_event_a_cancelation(bigint) RETURNS BOOLEAN AS $BODY$
DECLARE
  BEGIN
  RAISE DEBUG 'Performing consumptions_events_is_event_a_cancelation(%)', $1;
  RETURN EXISTS (
    SELECT true
    FROM consumptions_events ce
    where id = $1 and operation = 'canceled'
  );
  END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;
  -- Cancelation events must be leaf events: they have no childs
ALTER TABLE consumptions_events ADD CONSTRAINT consumptions_events_cancelation_are_leaf_events CHECK (NOT consumptions_events_is_event_a_cancelation(parent_id));


-- PARA LOS DELETES SE PUEDEN USAR TRIGGERS

CREATE or replace FUNCTION fake_delete() RETURNS TRIGGER AS $$
      BEGIN
          RAISE NOTICE '%',OLD;
          --RAISE SQLSTATE '23514'; -- LO de abajo es equivalnete y además puedo poner un mensaje!
          RAISE check_violation USING MESSAGE = 'No se puede borrar: ' || OLD.id;
    RAISE EXCEPTION 'EPA!!! QUE ME BORRAS?!', ALFRED_DB_ERROR_CODE_001; -- O Haces esto (no se borra y falla)
          RETURN NULL;  -- o devolves null (no se borra y no falla)
      END;
$$ LANGUAGE 'plpgsql';


CREATE TRIGGER fake_delete_trigger BEFORE DELETE
   ON services_sessions FOR EACH ROW
   EXECUTE PROCEDURE fake_delete();

CREATE TRIGGER fake_delete_trigger BEFORE DELETE
   ON services_sessions FOR EACH STATEMENT
   EXECUTE PROCEDURE public.fake_delete();

rollback;
begin;

select * from services_sessions;
delete from services_sessions where id = 1;

-- MANEJANDO DATES Y TIMESTAMPS...

-- para dado un instntante obtener el domingo que le precede
select ((TIMESTAMP '2018-08-11 00:00' at time zone '-03:00')::date - extract(dow from (TIMESTAMP '2018-08-11 00:00' at time zone '-03:00'))::int) -- el restultado es un date sin timezone, para pasarlo a timestamp hay que castearlo
-- CASTEANDO

cast estilo 1) select ((TIMESTAMP '2018-08-11 00:00' at time zone '-03:00')::date - extract(dow from (TIMESTAMP '2018-08-11 00:00' at time zone '-03:00'))::int)::timestamp at time zone '-03:00'
case estilo 2) select cast(((TIMESTAMP '2018-08-11 00:00' at time zone '-03:00')::date - extract(dow from (TIMESTAMP '2018-08-11 00:00' at time zone '-03:00'))::int) as timestamp)



-- JUGANDO CON LOS JOINS...


 CREATE TABLE alpha
(
  id serial NOT NULL,
  code text NOT NULL DEFAULT 'ALPHA',
  CONSTRAINT alfa_pkey PRIMARY KEY (id)
);

 CREATE TABLE beta
(
  id serial NOT NULL,
  alpha_id bigint,
  code text NOT NULL DEFAULT 'BETA',
  CONSTRAINT beta_pkey PRIMARY KEY (id),
  CONSTRAINT beta_alpha_fkey FOREIGN KEY (alpha_id) REFERENCES alpha (id)
);

insert into alpha (code) values ('A2');
insert into beta (code,alpha_id) values ('B1',1)

select *
from alpha a
 join beta b on b.alpha_id = a.id


where b.id is null

select *
from alpha a
full outer join beta b on b.alpha_id = a.id
where b.id is not null and a.id is not null

---------- CHECKS


CREATE TABLE pepe (
  id serial,
  state text NOT NULL
);
ALTER TABLE pepe ADD CONSTRAINT pepe_pk PRIMARY KEY (id);

ALTER TABLE pepe ADD CONSTRAINT pepe_check
CHECK (pepe_check_func(id));

-- Notar que el argumento id (luego parametro assigned_id) puede corresponderse con un id existente (update) o con un id asignado (insert)
CREATE OR REPLACE FUNCTION pepe_check_func(assigned_id bigint) RETURNS boolean AS
$BODY$
  DECLARE
    temp_row record;
  BEGIN
    RAISE INFO 'Performing seq scan on table "pepe"..., record_id id is %',record_id;
    FOR temp_row IN
        SELECT id,state
        FROM pepe
    LOOP
        RAISE INFO '(id,state) = (%,%)',temp_row.id, temp_row.state;
    END LOOP;
    RETURN TRUE;
  END;
$BODY$ LANGUAGE plpgsql IMMUTABLE;


insert into pepe (state) values ('granted');

-- TRABAJAR CON HORAS RELOJ


with cte as (
  select '4:00'::varchar as clock_from, '14:00'::varchar as clock_to
)
select (clock_to::time - clock_from::time) as duration
from cte

select '13:00'::time - '9:00'::time as duration;


-- VALORES NULLABLES


with cte as (
  select 'c1'::text as c1,null::text as c2
  union
  select 'c2'::text as c1,null::text as c2
)
select *
from cte
where c2 is NOT distinct from NULL -- puede pasarse un valor en vez de NULL

SELECT *
FROM (VALUES ('c1', null), ('c2', null))
AS names(field_1, field_2)
where field_1 is distinct FROM NULL


-- NOTAR QUE:
-- esta consulta es equivalente a
SELECT id
FROM appointments
WHERE patient_id is not distinct from null
except
-- esta otra!
SELECT id
FROM appointments
WHERE patient_id is null

------------------------
-- FUNCIONES WINDOW PARA CALCULAR VALORES DE UNA FILA QUE DEPENDEN DE OTRA DE LA MISMA TABLA

with cte as (
  select 1 as id, 1 as service_id, 10 as begins
  union
  select 2 as id, 1 as service_id, 20 as begins
  union
  select 3 as id, 1 as service_id, 30 as begins
  union
  select 4 as id, 2 as service_id, 40 as begins
)
select *, (
  SELECT begins
  FROM cte cte2
  WHERE cte2.service_id = cte1.service_id AND cte2.begins > cte1.begins
  ORDER BY begins ASC
  LIMIT 1
) as ends, lead(begins) over (partition by service_id order by begins asc)
from cte cte1
order by begins, ends asc


with cte as (
  select 1 as id, 1 as service_id, 10 as begins
  union
  select 2 as id, 1 as service_id, 20 as begins
  union
  select 3 as id, 1 as service_id, 30 as begins
  union
  select 4 as id, 2 as service_id, 40 as begins
)
select *, lead(begins) over (partition by service_id order by begins) as ends
from cte cte1
order by begins, ends asc


--- encontrar maximo dentro de una particion
with fly as (
  select 1 as id, 1 as service_id, 10 as begins
  union
  select 2 as id, 1 as service_id, 20 as begins
  union
  select 3 as id, 1 as service_id, 30 as begins
  union
  select 4 as id, 2 as service_id, 40 as begins
)
select *
from fly f1
where not exists ( -- no encontrar otro registro distinto (f1.id <> f2.id) que partenezca a la misma partición (f1.service_id = f2.service_id) cuyo f1.begins < f2.begins
  select true
  from fly f2
  where f1.id <> f2.id
    and f1.service_id = f2.service_id
    and f1.begins < f2.begins
)

select *
from (
  with cte as (
    select 1 as id, 1 as service_id, 10 as begins
    union
    select 2 as id, 1 as service_id, 20 as begins
    union
    select 3 as id, 1 as service_id, 30 as begins
    union
    select 4 as id, 2 as service_id, 40 as begins
  )
  select *, max(begins) over (PARTITION BY service_id) as max_begins_IN_service_partition -- con max(begins) sobre la partición de objetos con mismo service_id y dejamos ese valor como una nueva columna
  from cte cte1
) as temporal
where begins = max_begins_IN_service_partition -- aca se filtra por mismo valor


---- USANDO WINDOW FUCNTION, PARA OBTENER PARES DE ELEMENTOS QUE IDENTIFIQUEN PARES DE SEQUENCIA de usuarios creados en el sistema ordenados por tiempo (anterior, posterior) (Y así armar cadena de usuarios)
select id,next_id, org_id
from (
  select id,lead(id) OVER (PARTITION BY org_id ORDER BY created) AS next_id,org_id
  from sessions
) as sessions_view
where org_id = 21


select org_id, array_agg(row(next_id,to_timestamp(created/1000)))
from (
  select id,lead(id) OVER (PARTITION BY org_id ORDER BY created) AS next_id,org_id,created
  from sessions
) as sessions_view
group by org_id
having count(org_id) = 10

-- se puede usar el having count(*) para filtrar y poder ver organizaciones según su cantidad de usuarios registrdos
-- en mi local la consulta anterior arroja la org_id 29 con 10 empleados, solo basta hacer la consulta de abajo para verficarlo!
  select count(*)
  from sessions
  where org_id = 29

-----------
-- FORMAS DE BUSCAR EL MAXIMO

-- TRADICIONA
with test_table as (
  select 1 as id, 10 as amount
  union
  select 2 as id, 20 as amount
  union
  select 3 as id, 30 as amount
  union
  select 4 as id, 40 as amount
)
select *
from test_table
where amount = (
  select MAX(amount) as field_on_fly
  from test_table
)


-- USANDO LATERAL
with test_table as (
  select 1 as id, 10 as amount
  union
  select 2 as id, 20 as amount
  union
  select 3 as id, 30 as amount
  union
  select 4 as id, 40 as amount
)
select *
from test_table,
lateral (
  select MAX(amount) as field_on_fly
  from test_table
) as table_on_the_fly
where field_on_fly = amount


-- Entendiendo el LATERAL usando func json_array_elements

with cte as (
  select 1 as id, '[{"atrib":1},{"atrib":2}]'::json as data, 10 as begins
  union all
  select 2 as id, '[{"atrib":3}]'::json as data, 20 as begins
  union all
  select 3 as id,  '[{"atrib":5},{"atrib":6}]'::json as data, 30 as begins
  union all
  select 4 as id,  '[{"atrib":7}]'::json as data, 40 as begins
)
select *
from cte
cross join lateral json_array_elements(data)

-- OTRA EJEMPLO
with cte as (
  select 1 as id, 1 as service_id, '[10,20]'::jsonb as values
  union
  select 2 as id, 1 as service_id, '[1,2,3]'::jsonb as values
)
select *
from cte
cross join jsonb_array_elements(values)
cross join (select 1 union select 2 union select 3) as otra_table


  -- Es similar a

with cte as (
  select 1 as id, '[{"atrib":1},{"atrib":2}]'::json as data, 10 as begins
  union all
  select 2 as id, '[{"atrib":3}]'::json as data, 20 as begins
  union all
  select 3 as id,  '[{"atrib":5},{"atrib":6}]'::json as data, 30 as begins
  union all
  select 4 as id,  '[{"atrib":7}]'::json as data, 40 as begins
)
select *
from cte, json_array_elements(data)

-- pues la ","  impliciamente esta haciendo la junta como un "cross join lateral"


-- APLICANDOLO EN ALFRED

SELECT ssas.id, ssas.service_id,  ssas.session_id,  ssas.location_id,  ssas.substitute_staff_id,  ssas.begins, lead(ssas.begins) over (partition by ssas.service_id,ssas.session_id order by ssas.begins) as ends
FROM services_sessions_availabilities_sets AS ssas

EXCEPT

SELECT ssas.id, ssas.service_id,  ssas.session_id,  ssas.location_id,  ssas.substitute_staff_id,  ssas.begins, lookup.ends
FROM services_sessions_availabilities_sets AS ssas
LEFT JOIN LATERAL
  (
    SELECT begins as ends
    FROM services_sessions_availabilities_sets ssas2
    WHERE ssas.session_id = ssas2.session_id AND ssas.service_id = ssas2.service_id AND ssas2.begins > ssas.begins
    ORDER BY begins ASC
    LIMIT 1
) as lookup ON TRUE

--CURIOSIDAD, setencias equivalentes en selección, pero distintas en proyección
select *
from billables b
inner join products p on p.id = b.product_id
where p.package = 'cesan' and b.center_id = 18

select *
from billables b
where b.center_id = 18 and exists (
  select true
  from products p
  where p.id = b.product_id and p.package = 'cesan'
)



----- GENRANDO COLUMNA ON THE FLY, usanddo lateral vs corralted query explicita en el select


-- LATERAL

select id, appointment_org_name.name as org_name
from appointments a,
LATERAL (
  select name
  from organizations o
  where o.id = (
    select org_id
    from sessions s
    where s.id = (
      select session_id
      from appointments a2
      where a2.id = a.id
    )
  )
) AS appointment_org_name
where 0 <> (
  SELECT count(*)
  FROM (
    SELECT UNNEST(a.practices_ids) as practice_id
  ) AS practice_ids
  WHERE NOT EXISTS (
    SELECT *
    FROM practices p
    WHERE p.id = practice_id
  )
)




--  corralted query explicita en el select

select id, (
  select name
  from organizations o
  where o.id = (
    select org_id
    from sessions s
    where s.id = (
      select session_id
      from appointments a2
      where a2.id = a.id
    )
  )
) as org_name
from appointments a
where 0 <> (
  SELECT count(*)
  FROM (
    SELECT UNNEST(a.practices_ids) as practice_id
  ) AS practice_ids
  WHERE NOT EXISTS (
    SELECT *
    FROM practices p
    WHERE p.id = practice_id
  )
)



-- FORMAS DE USAR EL LATERAL

with cte as (
  select 1 as id, 1 as org_id
  union
  select 2 as id, 2 as org_id
  union
  select 3 as id, 1 as org_id
)
select *
from cte
inner join lateral (select 1 ) other on true


with cte as (
  select 1 as id, 1 as org_id
  union
  select 2 as id, 2 as org_id
  union
  select 3 as id, 1 as org_id
)
select *
from cte,
lateral (select 1 ) as other

--- BUSCANDO DUPLICADOS

-- FORMA 1

select p1.sid,(p1.firstname || ' ' || p1.lastname) as name, p1.idtype, p1.id, p2.*, (p1.firstname || ' ' || p1.lastname) = p2.name as nameEquals
from people p1
INNER JOIN LATERAL (
  SELECT p2.sid, p2.firstname || ' ' || p2.lastname as name, p2.idtype, p2.id
  from people p2
  where p1.sid <> p2.sid and p1.id = p2.id and p1.idtype = p2.idtype
) as p2 on true
limit 1

-- FORMA 2

select *
from people p1
where exists (
  select *
  from people p2
  where p1.sid <> p2.sid and p1.id = p2.id and p1.idtype = p2.idtype
)

-- FORMA 3

SELECT
        id,billable_id, referenced_entity_context, referenced_entity_id, kind,
        ROW_NUMBER() OVER (
            PARTITION BY billable_id, referenced_entity_context,referenced_entity_id, kind
            ORDER BY id asc) =
        COUNT(*) OVER (
    PARTITION BY billable_id, referenced_entity_context,referenced_entity_id, kind) as esElUltimo
    FROM
        patient_supporting_documents WHERE NOT kind = 'appointment_attachment' AND billable_id IS NOT NULL
ORDER BY id, billable_id, referenced_entity_context,referenced_entity_id, kind

-- AGRUPANDO FECHAS

-- FORMA 1) (Transformando a string)

select to_char(TO_TIMESTAMP(created / 1000)::timestamp with time zone at time zone 'America/Argentina/Buenos_Aires', 'DD/MM/YYYY HH24:MI:SS')
from studies

-- FORMA 2) (Transformando a date)

select to_timestamp(created/1000)::date
from studies

select (to_timestamp(created/1000)::timestamp with time zone at time zone 'America/Argentina/Buenos_Aires')::date
from studies


-- BUSCANDO ELEMENTOS DE UN TABLA QUE NO EsTEn REFERENCIADO EN OTRA.. SE USa _index_ como columna macheadora

  -- FORMA 1

  with locations as (
      select items.name, -1+row_number() over () as _index_
      from (
        select unnest(ARRAY['AA',
                               'BB'])
      ) as items(name)
  ), morphologies as (
    select items.name, -1+row_number() over () as _index_
    from (
      select unnest(ARRAY[
        '11'])
    ) as items(name)
  )
  select *
  from locations l
  left join morphologies m on m._index_ = l._index_
  where m.name is null

  select *
  from patients p
  where not exists (
    select true
    from patient_sessions ps
    where ps.patient_id = p.id
  )
  limit 2

  --- FORMA 2

  with locations as (
      select items.name, -1+row_number() over () as _index_
      from (
        select unnest(ARRAY['AA',
                               'BB'])
      ) as items(name)
  ), morphologies as (
    select items.name, -1+row_number() over () as _index_
    from (
      select unnest(ARRAY[
        '11'])
    ) as items(name)
  )
  select *
  from locations l
  where not exists (
    select true
    from morphologies m
    where m._index_ = l._index_
  )

  select *
  from patients p
  left join patient_sessions ps on ps.patient_id = p.id
  where ps is null
  limit 2



-- formas de generar tablas on the fly

-- con tabla temporal
SELECT *
FROM (
  select 1 as id
  union
  select 2 as id
  union
  select 3 as id
) AS fly

-- con common table expresion
with fly as (
  select 1 as id
  union
  select 2 as id
  union
  select 3 as id
)
select *
from fly


-- UPDATE CON REFRENCIAS A TABLAS QUE SE NECESITAN MATCHEAR CON LA TABLA DEL UPDATE (TARGET TABLE)

-- FORMA 1 (SUBSELECT)
UPDATE studies s
SET items = (items)::jsonb ||
  json_build_object('infoExtra', ((items->'infoExtra')::jsonb || json_build_object('studyDate', (
    select begins
    FROM gastro_studies gs
    WHERE gs.id = (SELECT origin_study FROM biopsys WHERE biopsy_study_id = s.id)))::jsonb )
  )::jsonb
WHERE s.kind = 'biopsy'

-----

-- FORMA 1 (UPDATE FROM)

UPDATE studies s
SET items = (items)::jsonb || json_build_object('infoExtra', (items->'infoExtra')::jsonb || json_build_object('studyDate', subquery.begins)::jsonb)::jsonb
FROM (
  SELECT begins, biopsy_study_id
  FROM gastro_studies gs
  INNER JOIN biopsys b on b.origin_study = gs.id
) AS subquery
WHERE s.kind = 'biopsy' AND subquery.biopsy_study_id = s.id

--- AGRUPANDO... DOS FORMAS DISTINAS DE OBTENER UNA COLUMNA Que no esta en el group by

-- Forma 1 (subquery a lo cabeza usando la columna de agrupación) [Parace que esta chotada es mejor...]
select (select name from organizations where id = org_id), org_id, count(*)
from patients
group by org_id
order by 3 desc

-- Forma 2 (NO funciona si hago group by org_id de patients, parece que detecta que id es primaria y entoces permite agregar columnas que tengan dependencia funcional)
select o.name, o.id, count(*)
from patients
inner join organizations o on o.id = org_id
group by o.id
order by 3 desc

-- CALCULAR EDAD

SELECT age(now(), to_timestamp(UNIX_TIMESTAMP))

  -- ESPECIFICANDO TIME ZONE!

SELECT age(now()::timestamp with time zone at time zone 'America/Argentina/Buenos_Aires', to_timestamp(1593634500)::timestamp with time zone at time zone 'America/Argentina/Buenos_Aires')


-- ESTUDIANDO EQUIVALENCIAS DE COMO JOINEAR TABLE FUNCTIONS

-- SIN LATERAL

select day
from fertility_incubations
cross join
  jsonb_array_elements(embryo_incubator_days)
 as day
where patient_id = 99013
limit 1

-- CON LATERAL
select day.status
from fertility_incubations
cross join lateral (
  select jsonb_array_elements(embryo_incubator_days) as status
) as day
where patient_id = 99013
limit 1


-- OBTENER INDICES ASOCIADOS

-- forma 1: usando `with ordinality`

with cte as (
  select 1 as id, '[{"atrib":1},{"atrib":2}, {}]'::json as json_data, 10 as begins
  union all
  select 3 as id,  '[{"atrib":5},{"atrib":6}, {"2":2}]'::json as json_data, 30 as begins
)
select id, json_agg(json_element.value), begins
from cte
cross join lateral (
  select *
  from json_array_elements(json_data) with ordinality as json_element(value)
) as json_element
where json_element.ordinality != 1
group by id, begins

-- forma 2: (más antigua!

with cte as (
  select 1 as id, '[{"atrib":1},{"atrib":2}, {}]'::json as data, 10 as begins
  union all
  select 3 as id,  '[{"atrib":5},{"atrib":6}, {"2":2}]'::json as data, 30 as begins
)
select id, json_agg(json_element.value), begins
from cte
cross join lateral (
  select value, -1+row_number() over () as _index_
  from json_array_elements(data) as json_element(value)
) as json_element
where json_element._index_ != 1
group by id, begins

--- GENERANDO ARREGLOS (NATIVOS Y JSON)

  -- (1) ARREGLO NATIVO DE POSTGRES
  SELECT psa.id, psa.patient_id, psa.created, psa.current_amount, psa.currency_id,
    CASE WHEN count(psat) = 0 THEN ARRAY[]::RECORD[] ELSE array_agg(psat) END as transactions
  FROM patient_saving_accounts psa
  LEFT JOIN LATERAL (
    SELECT psat.id, psat.patient_saving_account_id, psat.created, psat.amount, psat.currency_id, psat.origin_context, psat.session_id, psat.consumption_id, psat.cash_account_transaction_id, psat.invoice_billing_info_id, psat.details,
      sum(amount) OVER accumulative_values AS current_amount
    FROM patient_saving_account_transactions psat
    WHERE psa.id = psat.patient_saving_account_id
    WINDOW accumulative_values AS (PARTITION BY psat.patient_saving_account_id ORDER BY psat.created ASC)
  ) psat ON TRUE
  GROUP BY psa.id

  -- (2) ARREGLO JSON
  SELECT psa.id, psa.patient_id, psa.created, psa.current_amount, psa.currency_id,
    CASE WHEN count(psat) = 0 THEN '[]' ELSE json_agg(psat) END as transactions
  FROM patient_saving_accounts psa
  LEFT JOIN LATERAL (
    SELECT json_build_object(
          'id', psat.id,
          'patientSavingAccountId', psat.patient_saving_account_id,
          'created', psat.created,
          'amount', psat.amount,
          'currencyId', psat.currency_id,
          'originContext', psat.origin_context,
          'sessionId', psat.session_id,
          'consumptionId', psat.consumption_id,
          'cashAccountTransactionId', psat.cash_account_transaction_id,
          'invoiceBillingInfoId', psat.invoice_billing_info_id,
          'details', psat.details,
          'current_amount', sum(amount) OVER accumulative_values
        )
    FROM patient_saving_account_transactions psat
    WHERE psa.id = psat.patient_saving_account_id
    WINDOW accumulative_values AS (PARTITION BY psat.patient_saving_account_id ORDER BY psat.created ASC)
  ) psat ON TRUE
  GROUP BY psa.id

  with locations_1 as ( -- usando funciones ventanas para generar indice
  select item.name, -1+row_number() over () as _index_
  from (
    select unnest(ARRAY['Ángulo gástrico',
              'Bulbo duodenal',
              'Curvatura mayor cuerpo estómago',
              'Curvatura menor cuerpo estómago',
              'Curvatura mayor antro gástrico',
              'Curvatura menor antro gástrico',
              'Esofago superior',
              'Esofago medio',
              'Esofago inferior',
              'Otro',
              'Segunda porción de duodeno',
              'Techo gástrico'
            ])
  ) as item(name)
), locations_2 as ( --usando with ordanility, https://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number
  select item.name, item.index
  from unnest(ARRAY['Ángulo gástrico',
              'Bulbo duodenal',
              'Curvatura mayor cuerpo estómago',
              'Curvatura menor cuerpo estómago',
              'Curvatura mayor antro gástrico',
              'Curvatura menor antro gástrico',
              'Esofago superior',
              'Esofago medio',
              'Esofago inferior',
              'Otro',
              'Segunda porción de duodeno',
              'Techo gástrico'
        ])
  with ordinality as item(name, index)
)
select *
from locations_2,locations_1

-- CASO DE TIMEZONES USANDO FUNCION AGE

-- esto sirve para observar diferencias entre age(now(),timestamp) vs age(timestamp)
with cte as (
  select 622620000 as unix_timestamp
  union
  select -1018375200 as unix_timestamp
  union
  select 149234400 as unix_timestamp
)
select unix_timestamp,
  to_timestamp(unix_timestamp),
  age(now(), to_timestamp(unix_timestamp)),
  age(to_timestamp(unix_timestamp)),
from cte

-- dos conclusiones
-- (1) con un solo argumento la función age asume como parametro un momento correspondiente a la medianoche del día actual y (2) ese momento esta en UTC
-- (2) el momento esta asociado a la zona horaria UTC, lo que hace que se observen diferencias si se determina la edad para otra zona horaria

select age(now(), '2000-01-01'::timestamp),
       age('2000-01-01'::timestamp),
       -- si se usa un solo argumento, se compara contra la medianoche de la fecha actual
       age(medianoche_actual.utc,'2000-01-01'::timestamp),
       age('2000-01-01'::timestamp) = age(medianoche_actual.utc,'2000-01-01'::timestamp), -- true (pues age con un argumento toma la fecha actual en UTC, que es la misma que se le pasa cuando se invoca age con dos argumentos)
       age(medianoche_actual.arg,'2000-01-01'::timestamp),
       age('2000-01-01'::timestamp) = age(medianoche_actual.arg,'2000-01-01'::timestamp) -- false (pues age esta usando internamente UTC para la fecha actual y la fecha que se la pasa como argumento esta en GMT-3)
from (
    select
        current_date::timestamp as utc,
        current_date::timestamp at time zone 'America/Argentina/Buenos_Aires' arg
) as medianoche_actual