那些有用的SQL查询

 
--替代wm_conca()
select ( select listagg(column_name,',') within group(order by column_id)column_name from all_tab_columns where owner='SCHEMA_NAME' and table_name='TNAME') from tname;

专用行转列pivot/unpivot

select * from (
     select times_purchased as "Puchase Frequency", state_code
     from customers t
  )
  pivot 
  (
     count(state_code)
     for state_code in ('NY' as "New York",'CT' "Connecticut",
            'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri")
  )
  order by 1
  /

  Puchase Frequency   New York Connecticut New Jersey    Florida   Missouri
  ----------------- ---------- ----------- ---------- ---------- ----------
          0      16601         90           0          0          0
          1      33048        165           0          0          0
          2      33151        179           0          0          0
          3      32978        173           0          0          0
          4      33109        173           0          1          0
  ...  

  and so on ...

select value
from
(
    (
        select
            'a' v1,
            'e' v2,
            'i' v3,
            'o' v4,
            'u' v5
        from dual
    )
    unpivot
    (
        value
        for value_type in
            (v1,v2,v3,v4,v5)
    )
)

--Epoch, also known as Unix timestamps, is the number of seconds (not milliseconds!) that have elapsed since January 1, 1970 at 00:00:00 GMT (1970-01-01 00:00:00 GMT). 

--In many modern programming languages, date objects make use of epoch as a starting point from which they compute the inner date value. For example, in Java, the java.lang.Date class is set with the number of milliseconds that have elapsed since epoch.


create or replace function epoch_to_date(in_number NUMBER) return timestamp is
begin 
  return(TO_DATE('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss') + in_number/(86400*1000) +TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))/24);
end epoch_to_date;

select epoch_to_date(1567242000000) from dual;

create or replace function date_to_unix(in_date IN DATE) return number is 
begin 
  return( (in_date -TO_DATE('1970-01-01','yyyy-mm-dd hh24:mi:ss'))*86400*1000 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600*1000);
end unix_to_oracle;

select date_to_unix(to_date('2019-08-31 00:00:00','yyyy-mm-dd hh24:mi:ss')) from dual;