Q：Count the current week records from multiple table in Postgresql and Talend

Q：在Postgresql，人才多表算本周的记录

I have multiple tables in Postgresql. Say , A table has column A,column B,column C,refresh_date,fiscal week. B table has column D,column E,column B,fiscal week,refresh_date. I want to find the total count of records for the current week for table A and the total count of column E from table B for the current week. I am using Talend for data loading from table A and table B and those tables are in Postgresql. Also, if column E has values equal to zero for that current week then it should send a mail to myself.
I want to create a generic code for this as this is for table A and table B, there will be multiple tables used similar to this. How to do this in Talend and Postgresql?

I have multiple tables in Postgresql. Say , A table has column A,column B,column C,refresh_date,fiscal week. B table has column D,column E,column B,fiscal week,refresh_date. I want to find the total count of records for the current week for table A and the total count of column E from table B for the current week. I am using Talend for data loading from table A and table B and those tables are in Postgresql. Also, if column E has values equal to zero for that current week then it should send a mail to myself.
I want to create a generic code for this as this is for table A and table B, there will be multiple tables used similar to this. How to do this in Talend and Postgresql?

answer1：

回答1：

I've created something like your need a few months ago, and it basically executes a random query and parses the resultset and stores it denormalized in a database table. Note that I use enterprise Talend that has a neat feature called Dynamic Schema:
https://help.talend.com/pages/viewpage.action?pageId=190513179

So where should we start?
My typical query looks like this:

select pk1 as rcr_grby_pk1, pk2 as rcr_grpby_pk2,
count(*) as cnt, sum(amount) as sum_amount
from mySchema.myTable
group by pk1, pk2

Obviously the Select query could be anything, can contain any number of columns. We execute it and store the results in a table that would look like this:

PS: I know Float is a bad choice to store numbers, but hadn't had time to rework it, and it still gives acceptable results.

I've created something like your need a few months ago, and it basically executes a random query and parses the resultset and stores it denormalized in a database table. Note that I use enterprise Talend that has a neat feature called Dynamic Schema:
https://help.talend.com/pages/viewpage.action?pageId=190513179

So where should we start?
My typical query looks like this:

select pk1 as rcr_grby_pk1, pk2 as rcr_grpby_pk2,
count(*) as cnt, sum(amount) as sum_amount
from mySchema.myTable
group by pk1, pk2