ASAMA VIETNAM BITCOIN

select "DeviceDtm", ((dc."DisplayName") || $1 || "ParameterName") as "StationParameter", ((s."StationName") || $2 || "ParameterName") as "ParameterName", rm."HighStdLimit", "NormalizedValue" FROM registerdatapast7days rd INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId" INNER JOIN "Station" s ON dc."StationId" = s."StationId" AND s."MonitoringType" = $3 INNER JOIN "RegisterMap" rm ON rd."DeviceId" = rm."DeviceId" and rd."RegisterId" = rm."RegisterId" WHERE rm."ELV" > $4 AND rd."NormalizedValue" > rm."ELV" Order By 1, 2

COPY "RegisterDataSync" ("DeviceId", "RegisterId", "DeviceDtm", "RegisterValue", "NormalizedValue", "ServerDtm", "Quality") FROM STDIN (FORMAT BINARY)

SELECT cl."LocationId", cl."CpcbIndustryCode", dc."StationId", cs."CpcbStationCode", rd."DeviceId", COALESCE(cp."CpcbAltDeviceCode", cd."CpcbDeviceCode") as "CpcbDeviceCode", rd."RegisterId", cp."CpcbParameterCode", cp."CpcbUnit", rd."DeviceDtm", round(cast("NormalizedValue" as numeric), $1) as "NormalizedValue", rd."Quality" FROM "CpcbSyncTracker" rd INNER JOIN "PcbParameter" cp ON cp."RegisterId" = rd."RegisterId" and cp."CpcbPrmIsActive" = $2 INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId" AND dc."DeviceState" >= $3 INNER JOIN "PcbDevice" cd ON cd."DeviceId" = rd."DeviceId" and cd."CpcbDevIsActive" = $4 INNER JOIN "Station" s ON dc."StationId" = s."StationId" INNER JOIN "PcbStation" cs ON cs."StationId" = dc."StationId" and cs."CpcbStnIsActive" = $5 INNER JOIN "PcbLocation" cl ON s."LocationId" = cl."LocationId" and cl."CpcbIndIsActive" = $6 WHERE rd."Uploaded" = $7 AND rd."DeviceDtm" >= ($8::timestamp) AND rd."DeviceDtm" < ($9::timestamp) ORDER BY cs."CpcbStationCode", cd."CpcbDeviceCode", cp."CpcbParameterCode"

UPDATE "CpcbSyncTracker" SET "Uploaded" = $1, "UploadedDtm" = date_trunc($2, now()::timestamp) WHERE "DeviceDtm" = ($3::timestamp)

Covered by index on (DeviceDtm)
Rows: 3279502
Row progression: 3279502, 169

Row estimates
- DeviceDtm (=): 169

Existing indexes
- DeviceId, RegisterId, DeviceDtm PRIMARY
- DeviceDtm

SELECT cl."LocationId", cl."SpcbIndustryCode", dc."StationId", cs."SpcbStationCode", rd."DeviceId", COALESCE(cp."SpcbAltDeviceCode", cd."SpcbDeviceCode") as "SpcbDeviceCode", rd."RegisterId", cp."SpcbParameterCode", cp."SpcbUnit", time_bucket($1, rd."DeviceDtm") + $2 as "DeviceDtm", round(avg(COALESCE(cast("RegisterValue" as numeric), $3)), $4) as "RegisterValue", round(avg(COALESCE(cast("NormalizedValue" as numeric), $5)), $6) as "NormalizedValue", last("Quality", rd."DeviceDtm") as "Quality" FROM "SpcbSyncTracker" rd INNER JOIN "PcbParameter" cp ON cp."RegisterId" = rd."RegisterId" and cp."SpcbPrmIsActive" = $7 INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId" AND dc."DeviceState" >= $8 INNER JOIN "PcbDevice" cd ON cd."DeviceId" = rd."DeviceId" and cd."SpcbDevIsActive" = $9 INNER JOIN "Station" s ON dc."StationId" = s."StationId" INNER JOIN "PcbStation" cs ON cs."StationId" = dc."StationId" and cs."SpcbStnIsActive" = $10 INNER JOIN "PcbLocation" cl ON s."LocationId" = cl."LocationId" and cl."SpcbIndIsActive" = $11 WHERE time_bucket($12, rd."DeviceDtm") + $13 = ($14::timestamp) GROUP BY cl."LocationId", cl."SpcbIndustryCode", dc."StationId", cs."SpcbStationCode", rd."DeviceId", cd."SpcbDeviceCode", cp."SpcbAltDeviceCode", rd."RegisterId", cp."SpcbParameterCode", cp."SpcbUnit", (time_bucket('15 minute', rd."DeviceDtm")) ORDER BY cs."SpcbStationCode", cd."SpcbDeviceCode", cp."SpcbParameterCode", (time_bucket($15, rd."DeviceDtm"))

SELECT dc."StationId", cs."SpcbStationCode", rd."DeviceId", COALESCE(cp."SpcbAltDeviceCode", cd."SpcbDeviceCode") as "SpcbDeviceCode", rd."RegisterId", cp."SpcbParameterCode", cp."SpcbUnit", time_bucket($1, rd."DeviceDtm") + $2 as "DeviceDtm", round(avg(COALESCE(cast("RegisterValue" as numeric), $3)), $4) as "RegisterValue", round(avg(COALESCE(cast("NormalizedValue" as numeric), $5)), $6) as "NormalizedValue", last("Quality", rd."DeviceDtm") as "Quality" FROM "SpcbSyncTracker" rd INNER JOIN "PcbParameter" cp ON cp."RegisterId" = rd."RegisterId" and cp."SpcbPrmIsActive" = $7 INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId" AND dc."DeviceState" >= $8 INNER JOIN "PcbDevice" cd ON cd."DeviceId" = rd."DeviceId" and cd."SpcbDevIsActive" = $9 INNER JOIN "Station" s ON dc."StationId" = s."StationId" INNER JOIN "PcbStation" cs ON cs."StationId" = dc."StationId" and cs."SpcbStnIsActive" = $10 WHERE time_bucket($11, rd."DeviceDtm") + $12 = ($13::timestamp) GROUP BY dc."StationId", cs."SpcbStationCode", rd."DeviceId", cd."SpcbDeviceCode", cp."SpcbAltDeviceCode", rd."RegisterId", cp."SpcbParameterCode", cp."SpcbUnit", (time_bucket('15 minute', rd."DeviceDtm")) ORDER BY cs."SpcbStationCode", cd."SpcbDeviceCode", cp."SpcbParameterCode", (time_bucket($14, rd."DeviceDtm"))

select rd."DeviceId", rd."RegisterId", "ParameterName", u."UnitName", rd."DeviceDtm" as "DeviceDtm", dc."StationId", s."StationName",s."MonitoringType", COALESCE(round(nullif(cast (rd."NormalizedValue" as numeric), $1), $2), $3) as "RegisterValue", "Quality", me."MiscKey" as "QualityDesc",Now() as "ServerDtm", "ParameterDesc", rm."UnitId", "RegisterAddr", "ReadRegisterLen", rm."HighStdLimit", rm."LowStdLimit", rm."ELV"   FROM "RegisterData" rd  INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId"  INNER JOIN "Station" s ON dc."StationId" = s."StationId"  INNER JOIN "RegisterMap" rm ON rd."DeviceId" = rm."DeviceId" and rd."RegisterId" = rm."RegisterId"  INNER JOIN "Unit" u ON rm."UnitId" = u."UnitId"  INNER JOIN "MiscEnum" me ON rd."Quality" = me."MiscValue" and me."MiscTag" = $4  WHERE rd."DeviceDtm" >= ($5::timestamp)  AND rd."DeviceDtm" < ($6::timestamp)  AND dc."StationId" = $7 Order By rd."DeviceId", rm."ReadOrder", rd."DeviceDtm"

select g."Date" + INTERVAL $1 as "Date",g."PM" from (select * from crosstab ($2, $3) as ct ("Date" timestamp , "PM" decimal) order by "Date" asc) as g

UPDATE "SpcbSyncTracker" SET "Uploaded" = $1, "UploadedDtm" = date_trunc($2, now()::timestamp) WHERE time_bucket($3, "DeviceDtm") + $4 = ($5::timestamp)

select time_bucket_gapfill($1, "DeviceDtm") as "DeviceDtm", "ParameterName", CASE WHEN Max("Quality") = $2 THEN $3 WHEN Max("Quality") = $4 THEN $5 ELSE round(avg(nullif(cast ("NormalizedValue" as numeric), $6)), $7) END as "RegisterValue"  FROM registerdata_15min rd  INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId"  INNER JOIN "Station" s ON dc."StationId" = s."StationId"  INNER JOIN "RegisterMap" rm ON rd."DeviceId" = rm."DeviceId" and rd."RegisterId" = rm."RegisterId"  INNER JOIN "Unit" u ON rm."UnitId" = u."UnitId"  WHERE "DeviceDtm" >= ($8::timestamp) -INTERVAL $9 AND "DeviceDtm" < ($10::timestamp)  AND dc."StationId" = $11 AND "ParameterName" IN ($12) Group By 1, 2 Order By 1, 2

select * from crosstab ($1, $2) as ct ("Date" timestamp , "PM" decimal) left join lateral(
select * from crosstab 
($3, $4) as ct ("QDate" timestamp ,"QPM" decimal)) q on ct."Date" = q."QDate"
order by ct."Date" asc

select * from crosstab ($1, $2) as ct ("Date" timestamp , "AAQMS#01-WPFA SO2" decimal,"AAQMS#02-CRM SO2" decimal,"AAQMS#03-PH#03 SO2" decimal,"AAQMS#04-PH#06 SO2" decimal,"Stack_11_Coke Plant Battery 11 Process SO2" decimal,"Stack_13_Sinter Plant-1 Waste Gas SO2" decimal,"Stack_15_Sinter Plant # 2 Waste Gas SO2" decimal,"Stack_18_Sinter Plant # 3Combined(Waste Gas&Dedusting) SO2" decimal,"Stack_1_Power House-3 & Boiler-07&08 SO2" decimal,"Stack_20_Sinter Plant#04-Combined(Waste Gas&Dedusting) SO2" decimal,"Stack_24_H-Blast Furnace Stove SO2" decimal,"Stack_2_Power House-4-Boiler-4 SO2" decimal,"Stack_55_I-Blast Furnance-Stove SO2" decimal,"Stack_6_Coke Plant Battery#07 SO2" decimal,"Stack_73_Power House-5-Boiler-A SO2" decimal,"Stack_74_Power House-5-Boiler-B&C SO2" decimal,"Stack_7_Coke Plant Battery#08 SO2" decimal,"Stack_80_C_Blast_Furnace_Stove SO2" decimal,"Stack_81_E_Blast_Furnace_Stove SO2" decimal,"Stack_82_F_Blast_Furnace_Stove SO2" decimal,"Stack_83_G_Blast_Furnace_Stove SO2" decimal,"Stack_84_Power_House_4_Boiler_1&2 SO2" decimal,"Stack_85_Power_House_4_Boiler_5 SO2" decimal,"Stack_86_Power_House_3_Boiler_5 SO2" decimal,"Stack_87_Power_House_3_Boiler_6 SO2" decimal,"Stack_8_Coke Plant Battery#09 SO2" decimal,"Stack_9_Coke Plant Battery 10 Process SO2" decimal) order by "Date" asc

select "DeviceDtm"+INTERVAL $1 as "DeviceDtm", ((s."StationName"  || $2 || "ParameterName")::text) as "StationParameter", CASE WHEN "Quality" = $3 THEN $4 ELSE COALESCE(round(nullif(cast ("NormalizedValue" as numeric), $5), $6), $7) END as "RegisterValue" FROM registerdata_15min rd INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId" INNER JOIN "Station" s ON dc."StationId" = s."StationId" INNER JOIN "RegisterMap" rm ON rd."DeviceId" = rm."DeviceId" and rd."RegisterId" = rm."RegisterId" INNER JOIN "Unit" u ON rm."UnitId" = u."UnitId" WHERE rd."DeviceDtm" >= ($8::timestamp)-INTERVAL $9 AND rd."DeviceDtm" < ($10::timestamp)  AND "ParameterName" IN ($11) Order By 1, 2

select time_bucket_gapfill($1, "DeviceDtm") as "DeviceDtm", "ParameterName", round(avg(nullif(cast ("NormalizedValue" as numeric), $2)), $3)  as "RegisterValue"  FROM registerdata_15min rd  INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId"  INNER JOIN "Station" s ON dc."StationId" = s."StationId"  INNER JOIN "RegisterMap" rm ON rd."DeviceId" = rm."DeviceId" and rd."RegisterId" = rm."RegisterId"  INNER JOIN "Unit" u ON rm."UnitId" = u."UnitId"  WHERE "DeviceDtm" >= ($4::timestamp)  AND "DeviceDtm" <= ($5::timestamp)  AND dc."StationId" = $6 AND "ParameterName" IN ($7) Group By 1, 2 Order By 1, 2

UPDATE "SpcbSyncTracker" SET "Uploaded" = $1, "UploadedDtm" = date_trunc($2, now()::timestamp) WHERE "DeviceId" IN (SELECT "DeviceId" From "PcbDevice" WHERE "SpcbDeviceCode" = $3) AND time_bucket($4, "DeviceDtm") + $5 = ($6::timestamp)

select jinfo->>$1 as nextcpcbsync,jinfo->>$2 as nextspcbsync, (select "DeviceDtm" from "RegisterData" ORDER BY "DeviceDtm" DESC LIMIT $3) as datasyncdatetime, (select COUNT(*) from "RegisterData" where "DeviceDtm" = (select d."DeviceDtm" from "RegisterData" d ORDER BY d."DeviceDtm" DESC LIMIT $4)) as noofregisterssynced FROM "SyncState"

SELECT dc."StationId", cs."CpcbStationCode", rd."DeviceId", COALESCE(cp."CpcbAltDeviceCode", cd."CpcbDeviceCode") as "CpcbDeviceCode", rd."RegisterId", cp."CpcbParameterCode", cp."CpcbUnit", rd."DeviceDtm", round(cast("NormalizedValue" as numeric), $1) as "NormalizedValue", rd."Quality" FROM "CpcbSyncTracker" rd INNER JOIN "PcbParameter" cp ON cp."RegisterId" = rd."RegisterId" and cp."CpcbPrmIsActive" = $2 INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId" AND dc."DeviceState" >= $3 INNER JOIN "PcbDevice" cd ON cd."DeviceId" = rd."DeviceId" and cd."CpcbDevIsActive" = $4 INNER JOIN "Station" s ON dc."StationId" = s."StationId" INNER JOIN "PcbStation" cs ON cs."StationId" = dc."StationId" and cs."CpcbStnIsActive" = $5 WHERE rd."Uploaded" = $6 AND rd."DeviceDtm" >= ($7::timestamp) AND rd."DeviceDtm" < ($8::timestamp) AND rd."DeviceId" IN (SELECT "DeviceId" FROM "DeviceConfig" WHERE "StationId" IN ( SELECT "StationId" FROM "Station" WHERE "LocationId" IN (SELECT "LocationId" FROM "PcbLocation" WHERE "CpcbIndustryCode"=$9)))ORDER BY cs."CpcbStationCode", cd."CpcbDeviceCode", cp."CpcbParameterCode"

select g."Date" + INTERVAL $1 as "Date",g."PM10",g."PM2.5",g."SO2",g."NOX",g."CO" from (select * from crosstab ($2, $3) as ct ("Date" timestamp , "PM10" decimal,"PM2.5" decimal,"SO2" decimal,"NOX" decimal,"CO" decimal) order by "Date" asc) as g

SELECT schemaname AS schema, t.relname AS table, ix.relname AS name, regexp_replace(pg_get_indexdef(i.indexrelid), $1, $2) AS columns, regexp_replace(pg_get_indexdef(i.indexrelid), $3, $4) AS using, indisunique AS unique, indisprimary AS primary, indisvalid AS valid, indexprs::text, indpred::text, pg_get_indexdef(i.indexrelid) AS definition FROM pg_index i INNER JOIN pg_class t ON t.oid = i.indrelid INNER JOIN pg_class ix ON ix.oid = i.indexrelid LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid WHERE schemaname IS NOT NULL ORDER BY 1, 2 /*pghero*/

select * from crosstab ($1, $2) as ct ("Date" timestamp , "PM10" decimal,"PM2.5" decimal,"SO2" decimal,"NOX" decimal,"CO" decimal) left join lateral(
select * from crosstab 
($3, $4) as ct ("QDate" timestamp ,"QPM10" decimal,"QPM2.5" decimal,"QSO2" decimal,"QNOX" decimal,"QCO" decimal)) q on ct."Date" = q."QDate"
order by ct."Date" asc

SELECT  time_bucket($1, rd."DeviceDtm") +  interval $2  AS "Norm15MinDtm", Max("ELV") as "ELV",avg(COALESCE(rd."NormalizedValue"::numeric, $3::numeric)) AS "NormalizedValue", max(rd."Quality") AS "Quality",s."StationDisplayName",dc."DisplayName" as "DeviceDisplayName", rm."ParameterName",u."UnitName",s."StationId" FROM "RegisterData" rd INNER JOIN "RegisterMap" rm  on rd."DeviceId" = rm."DeviceId" And rd."RegisterId" = rm."RegisterId" INNER JOIN "DeviceConfig" dc on rd."DeviceId"=dc."DeviceId" INNER JOIN "Station" s on dc."StationId"=s."StationId" INNER JOIN "Unit" u on u."UnitId"=rm."UnitId" WHERE rd."DeviceDtm" >= $4 And rd."DeviceDtm" < $5   And rm."ELV" > $6 and "Quality"=$7 And "StationDisplayName" NOT LIKE $8  GROUP BY  time_bucket($9, rd."DeviceDtm"), s."StationDisplayName","DeviceDisplayName",rm."ParameterName",u."UnitName",s."StationId" Having avg(COALESCE(rd."NormalizedValue"::numeric, $10::numeric)) > Max(rm."ELV")

select time_bucket_gapfill($1, "DeviceDtm") as "DeviceDtm", "ParameterName", max("Quality") as "Quality" FROM "RegisterData" rd  
   INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId" 
    INNER JOIN "Station" s ON dc."StationId" = s."StationId"  
    INNER JOIN "RegisterMap" rm ON rd."DeviceId" = rm."DeviceId" and rd."RegisterId" = rm."RegisterId"  
   INNER JOIN "Unit" u ON rm."UnitId" = u."UnitId"  WHERE "DeviceDtm" >= ($2::timestamp) 
    AND "DeviceDtm" <= ($3::timestamp)  AND dc."StationId" = $4 AND "ParameterName" 
    IN ($5) Group By 1, 2 Order By 1, 2

select time_bucket_gapfill($1, "DeviceDtm") as "DeviceDtm", "ParameterName", CASE WHEN Max("Quality") = $2 THEN $3 WHEN Max("Quality") = $4 THEN $5 ELSE round(avg(nullif(cast ("NormalizedValue" as numeric), $6)), $7) END as "RegisterValue"  FROM registerdata_15min rd  INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId"  INNER JOIN "Station" s ON dc."StationId" = s."StationId"  INNER JOIN "RegisterMap" rm ON rd."DeviceId" = rm."DeviceId" and rd."RegisterId" = rm."RegisterId"  INNER JOIN "Unit" u ON rm."UnitId" = u."UnitId"  WHERE "DeviceDtm" >= ($8::timestamp) -INTERVAL $9 AND "DeviceDtm" < ($10::timestamp)  AND dc."StationId" = $11 AND "ParameterName" IN ($12,$13,$14,$15,$16) Group By 1, 2 Order By 1, 2

select time_bucket_gapfill($1, "DeviceDtm") as "DeviceDtm", "ParameterName", CASE WHEN Max("Quality") = $2 THEN $3 WHEN Max("Quality") = $4 THEN $5 ELSE round(avg(nullif(cast ("NormalizedValue" as numeric), $6)), $7) END as "RegisterValue"  FROM "RegisterData" rd  INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId"  INNER JOIN "Station" s ON dc."StationId" = s."StationId"  INNER JOIN "RegisterMap" rm ON rd."DeviceId" = rm."DeviceId" and rd."RegisterId" = rm."RegisterId"  INNER JOIN "Unit" u ON rm."UnitId" = u."UnitId"  WHERE "DeviceDtm" >= ($8::timestamp) -INTERVAL $9 AND "DeviceDtm" < ($10::timestamp)  AND dc."StationId" = $11 AND "ParameterName" IN ($12) Group By 1, 2 Order By 1, 2

select time_bucket_gapfill($1, "DeviceDtm") as "DeviceDtm", "ParameterName", round(avg(nullif(cast ("NormalizedValue" as numeric), $2)), $3)  as "RegisterValue"  FROM registerdata_15min rd  INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId"  INNER JOIN "Station" s ON dc."StationId" = s."StationId"  INNER JOIN "RegisterMap" rm ON rd."DeviceId" = rm."DeviceId" and rd."RegisterId" = rm."RegisterId"  INNER JOIN "Unit" u ON rm."UnitId" = u."UnitId"  WHERE "DeviceDtm" >= ($4::timestamp)  AND "DeviceDtm" <= ($5::timestamp)  AND dc."StationId" = $6 AND "ParameterName" IN ($7,$8,$9,$10,$11) Group By 1, 2 Order By 1, 2

select g."Date" + INTERVAL $1 as "Date",g."PM10",g."PM2.5",g."SO2",g."NOX" from (select * from crosstab ($2, $3) as ct ("Date" timestamp , "PM10" decimal,"PM2.5" decimal,"SO2" decimal,"NOX" decimal) order by "Date" asc) as g

select time_bucket_gapfill($1, "DeviceDtm") as "DeviceDtm", "ParameterName", round(avg(nullif(cast ("NormalizedValue" as numeric), $2)), $3)  as "RegisterValue"  FROM "RegisterData" rd  INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId"  INNER JOIN "Station" s ON dc."StationId" = s."StationId"  INNER JOIN "RegisterMap" rm ON rd."DeviceId" = rm."DeviceId" and rd."RegisterId" = rm."RegisterId"  INNER JOIN "Unit" u ON rm."UnitId" = u."UnitId"  WHERE "DeviceDtm" >= ($4::timestamp)  AND "DeviceDtm" <= ($5::timestamp)  AND dc."StationId" = $6 AND "ParameterName" IN ($7) Group By 1, 2 Order By 1, 2

select time_bucket_gapfill($1, "DeviceDtm") as "DeviceDtm", "ParameterName", max("Quality") as "Quality" FROM "RegisterData" rd  
   INNER JOIN "DeviceConfig" dc ON rd."DeviceId" = dc."DeviceId" 
    INNER JOIN "Station" s ON dc."StationId" = s."StationId"  
    INNER JOIN "RegisterMap" rm ON rd."DeviceId" = rm."DeviceId" and rd."RegisterId" = rm."RegisterId"  
   INNER JOIN "Unit" u ON rm."UnitId" = u."UnitId"  WHERE "DeviceDtm" >= ($2::timestamp) 
    AND "DeviceDtm" <= ($3::timestamp)  AND dc."StationId" = $4 AND "ParameterName" 
    IN ($5,$6,$7,$8,$9) Group By 1, 2 Order By 1, 2