Home I want to display the jobs running on different clusters using SQL
Reply: 1

I want to display the jobs running on different clusters using SQL

Hello
1#
Hello Published in 2017-09-13 11:58:24Z

There are two tables job table and cluster table.Every cluster will have the different sub clusters.

LIKE Main cluster

cluster1     cluster2      cluster3
--------------------------------------
job1         job15           job 20
.              .
.
.
j14

1st cluster will have 14 jobs 2nd cluster will have 5 jobs 3rd cluster will have 5 jobs

Now I want to display the jobs running on different clusters (Including sub clusters) using PostgreSQL.

My table structure as follows

CREATE TABLE clusterdata(  
   clusterid bigint NOT NULL,
   sourceclustername character varying NOT NULL,
   clustername character varying NOT NULL
);

CREATE TABLE  job(
   clusterid bigint NOT NULL,
   jobname character varying NOT NULL,  
   sourcecluster bigint
);

Please help me...Thanx in advance

etsa
2#
etsa Reply to 2017-09-13 13:54:46Z

Assuming your sentence "1st cluster will have 14 jobs 2nd cluster will have 5 jobs 3rd cluster will have 5 jobs" you can use something like this:

WITH BASE AS (
        SELECT * , ROW_NUMBER() OVER (PARTITION BY sourcecluster, clusterid ORDER BY clusterid, jobname) AS RN
        , DENSE_RANK() OVER (PARTITION BY sourcecluster  ORDER BY clusterid) * CASE WHEN sourcecluster=0 THEN 0 ELSE 1 END AS RN_GROUP
        FROM job )
      SELECT A.clusterid AS SOURCE_CLUSTER, A.jobname AS CLUSTER_1
      , B.jobname AS CLUSTER_2
      , C.jobname AS CLUSTER_3
      FROM BASE A      
      LEFT JOIN BASE B ON B.sourcecluster = A.clusterid AND B.RN_group=1 AND A.RN=B.RN   
      LEFT JOIN BASE C ON C.sourcecluster = A.clusterid AND C.RN_group=2 AND B.RN=C.RN
      WHERE A.sourcecluster=0
      ORDER BY A.clusterid, A.RN, B.RN;

Output:

    source_cluster  cluster_1   cluster_2   cluster_3
1   1               job1    job15   job20
2   1               job2    job16   job21
3   1               job3    job17   job22
4   1               job4    job18   NULL
5   1               job5    NULL    NULL
6   4               job41   NULL    NULL
7   4               job42   NULL    NULL
8   4               job43   NULL    NULL
9   4               job44   NULL    NULL
10  4               job45   NULL    NULL
11  5               job51   job55   NULL
12  5               job52   job56   NULL
13  5               job53   job57   NULL
14  5               job54   job58   NULL
15  5               job55   NULL    NULL

Sample data:

INSERT INTO clusterdata VALUES (1, '0', 'clust1');
INSERT INTO clusterdata VALUES (2, 'clust1', 'clust1.1');
INSERT INTO clusterdata VALUES (3, 'clust1', 'clust1.2');
INSERT INTO clusterdata VALUES (4, '0', 'clust2');
INSERT INTO clusterdata VALUES (5, '0', 'clust3');
INSERT INTO clusterdata VALUES (6, 'clust3', 'clust3.1');

INSERT INTO job VALUES (1,'job1',0);
INSERT INTO job VALUES (1,'job2',0);
INSERT INTO job VALUES (1,'job3',0);
INSERT INTO job VALUES (1,'job4',0);
INSERT INTO job VALUES (1,'job5',0);
INSERT INTO job VALUES (2,'job15',1);
INSERT INTO job VALUES (2,'job16',1);
INSERT INTO job VALUES (2,'job17',1);
INSERT INTO job VALUES (2,'job18',1);
INSERT INTO job VALUES (3,'job20',1);
INSERT INTO job VALUES (3,'job21',1);
INSERT INTO job VALUES (3,'job22',1);

INSERT INTO job VALUES (4,'job41',0);
INSERT INTO job VALUES (4,'job42',0);
INSERT INTO job VALUES (4,'job43',0);
INSERT INTO job VALUES (4,'job44',0);
INSERT INTO job VALUES (4,'job45',0);
INSERT INTO job VALUES (5,'job51',0);
INSERT INTO job VALUES (5,'job52',0);
INSERT INTO job VALUES (5,'job53',0);
INSERT INTO job VALUES (5,'job54',0);
INSERT INTO job VALUES (5,'job55',0);
INSERT INTO job VALUES (6,'job55',5);
INSERT INTO job VALUES (6,'job56',5);
INSERT INTO job VALUES (6,'job57',5);
INSERT INTO job VALUES (6,'job58',5);
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.301437 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO