Skip to main content

Pivot Query in Postgresql is much better than Pivot_Table in Pandas

Pivot Query in Postgresql

select * from
crosstab(
        $$
         SELECT anonymous_id, message_id , timestamp, key, value
                 FROM pixels
                 WHERE tenant_id = 2
                      AND pixels.timestamp >= '2019-10-19 10:41:03.254968'
                      AND pixels.timestamp < '2019-10-20 10:41:03.254968'
                      AND message_id = ANY(SELECT message_id
                                           FROM pixels
                                           WHERE tenant_id = 2
                      AND pixels.timestamp >= '2019-10-19 10:41:03.254968'
                      AND pixels.timestamp < '2019-10-20 10:41:03.254968'
                                             AND key = 'event'
                                             AND value = 'init_banuba_success'
                     )
    AND (key = ANY(array['context.timezone','context.device.model','context.device.os']))
        $$,
        $$
         values ('context.timezone') ,
        ('context.device.model') ,
        ('context.device.os')
        $$)
 AS (
    anonymous_id TEXT,
    message_id TEXT,
    timestamp timestamp,
    context_device_model TEXT,
    context_device_os TEXT,
    context_timezone TEXT);


Comments

  1. Harrah's Cherokee Casino & Hotel - MapYRO
    HARRAH'S 군산 출장샵 CHEROKEE CASINO & HOTEL - 2021 Updated 777 Casino Drive Cherokee, 여주 출장마사지 NC 28719. Directions · (800) 정읍 출장샵 462-5000. 삼척 출장안마 Call Now · More Info. 광주광역 출장마사지 Hours, Accepts Credit Cards,

    ReplyDelete

Post a Comment

Popular posts from this blog

Non-Test vs Test Driven Developer in Golang

There's been a lot of debates on whether TDD is dead or not! I strongly believe that TDD is very essential in any serious Software Development endeavor, especially for any team which is currently embracing  MICRO-services architecture or might seek to transition to Micro-services later. TDD somewhat will drive you to work out better and better system design rather than just focus on increasing Code Coverage as a vanity metric. The more you aim to improve Testability and TDD compliance in every single piece of your software, the easier you can extend or modify any functionalities in your system. I'm gonna share some pieces of codes to help illustrate my points regarding how different they are in practiceLet's dive into those differences //Non-Test package client import ( "bitbucket.org/sakariai/sakari/proto" "go.elastic.co/apm/module/apmgrpc" "google.golang.org/grpc" ) type Volante struct { proto.VolanteClient conn